Selecting letters and characters.

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)

Selecting letters and characters.

Author

Topic

ashraff87
Starting Member

17 Posts

Posted - 2008-09-05 : 04:28:55

Hi, im currently looking to return all bad data in my phone_numbers table.This is anything that is not purely a number in the number column.

Im currently running this sql:


SELECT * FROM PHONE_NUMBERS
WHERE 
NUMBER LIKE '%[a-z]%'

However i am aware this will not return all bad data because an entry such as '£' wont come back. How can i include all symbols in the range? Ive tried doing it the other way as in:
NUMBER NOT LIKE '%[0-9]%' However bad data which isnt returned wouild be '0122834745 jklsdhfsof'

Any ideas? Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-05 : 04:45:12

WHERE NUMBER NOT LIKE '%[^0-9]%'

Madhivanan

Failing to plan is Planning to fail

ashraff87
Starting Member

17 Posts

Posted - 2008-09-05 : 04:55:53

quote:
Originally posted by madhivanan

WHERE NUMBER NOT LIKE '%[^0-9]%'

Hmm, Thank you, but that didn't seem to work, it returned numbers, please explain what the ^ is doing. Also I do allow spaces so '1223 4455' is a valid number. Using the code you gave me only whole numbers are returned as in 654654 but 548 441 isnt.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-09-05 : 05:45:00

try using space in where condition:

WHERE NUMBER NOT LIKE '%[^0-9 ]%'

Declare @Table table
( tbl varchar(30))
Insert into @Table

Select '3345 980' UNION ALL
Select '33980' UNION ALL
Select '33 980' UNION ALL
Select '33980'
Select * from @Table WHERE tbl NOT LIKE '%[^0-9 ]%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources