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.
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,35b71214a344574134I do a search such as:select * from tablewhere streetno between '2' and '8'Which returns all columns..... I need it to just return35b7Any 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.JimEveryday I learn something that somebody else already knew |
|
|
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? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-11-01 : 07:35:21
|
select * from tablewhere stuff(streetno,patindex('%[a-zA-Z]%',streetno)-1,0,'')*1 between 2 and 8MadhivananFailing to plan is Planning to fail |
|
|
|
|
|