Even numbered addresses in a range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Even numbered addresses in a range

Author

Topic

macext
Starting Member

2 Posts

Posted - 2009-02-19 : 17:14:08

Hi,

Is there a simple way to get only the even numbered adresses on Main st but only between 200 and 400 Main st?

I've been using:

like '%[02468] main%'

which gives me only even numbers,

between '200 main' and '400 main'

which gives me addresses between 200 and 400 main

I tried to add a BETWEEN in there as well for the 200 - 400main st range, but it wasn't working out. Can I use the Between and I am just messing it up?

like( ('%[02468] main%') and (between '200 main' and '400 main'))

too many parentheses?

If it has to be "programming" then I need alot of help.

TIA

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-19 : 19:07:46

Maybe someone has a better way to handle the 400 block but I had to put it on it's own line to cut it off for ending at 0.

Create table #temp(address varchar(20))

insert into #temp values('101 Main'),('202 Main'),('303 Main'),('505 Main')
insert into #temp values('1000 Main'),('400 Main'),('401 Main'), ('200 Main Street')

select * from #temp
where [address] like '[1,2,3][0-9][0-9] Main'
or [address] like '400 Main%'

Oh and I wasn't sure if you wanted just the even numbered addresses, if so use the modulo operator like below:

select * into #results from #temp
where [address] like '[1,2,3][0-9][0-9] Main'
or [address] like '400 Main%'

select * from #results
where LEFT(address,3)%2=0
Mike
"oh, that monkey is going to pay"