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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Between on an alpha numeric column

Author  Topic 

offspring22
Starting Member

38 Posts

Posted - 2010-10-28 : 18:29:11
So I have a table with a bunch of addresses. One field, streetno, has the house number which occasionally includes a letter as well. IE,

3
5b
7
12
14a
34
45
74
134

I do a search such as:

select * from table
where streetno between '2' and '8'

Which returns all columns..... I need it to just return

3
5b
7

Any suggestions on how to achieve this? The column is a varchar(9) if it helps.

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-28 : 19:39:08
you could try
where convert(tinyint,left(streetno,1)) between 2 and 8.
But that won't work for between, say, 10 and 50. For that you'd have to remove the alpha characters.



Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

offspring22
Starting Member

38 Posts

Posted - 2010-10-28 : 23:49:44
I'd be ok with not counting the alpha characters in the select query (just ignoring them), but not out of the data itself. Any suggestions on how to do that?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-01 : 07:35:21
select * from table
where stuff(streetno,patindex('%[a-zA-Z]%',streetno)-1,0,'')*1 between 2 and 8

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -