Query query!

[bugmenot]

Hi everyone,

I'm trying to write a stored procedure select statement which is a little beyond my experience level.

I have a table in the DB called addresses which contains the following fields:

• address, VARCHAR
• count, INT
• (a few more fields)

My stored procedure will take in a unique address in the following format:

• ####.####.####.####, VARCHAR

What I need to do is compare the passed in value against the [addresses].[address] values and return the matching row. The problem is I also need to check for for increments on the final four characters of the passed in address, upto the [addresses].[count].

Here is an example, the address table contains:

• address = 100.100.2225.100
• count = 5
• (a few more fields)

The stored procedure value passed in is 100.100.2225.103. In this case thie row should be returned as the passed in address 100.100.2225.103 <= 100.100.2225.(100+5)

I hope this makes sense to someone and is simple to do. This isn't for any specific project or work and is purely for my own personal geekery. My only other option is to do this in code.

Thanks in advance for any pointers, code, links or abuse.

[huangzhi]

Try code below:

declare @Ask varchar(255)

select @Ask = '100.100.2225.104'

create table #tmpAddr (
  Addr varchar(255),
  Inc int)

insert #tmpAddr select '100.100.2225.100', 5
insert #tmpAddr select '100.100.2225.103', 5
insert #tmpAddr select '100.100.2225.110', 5

select	left(Addr, len(Addr) - charindex('.', reverse(Addr))),
	right(Addr, charindex('.', reverse(Addr)) - 1)
  from #tmpAddr
  where @Ask between Addr and left(Addr, len(Addr) - charindex('.', reverse(Addr))) + '.' + cast(cast(right(Addr, charindex('.', reverse(Addr)) - 1) as int) + Inc as varchar)

drop table #tmpAddr