CODEWITHSUNDEEP
sqlsql-serversql-server-2012
BJJ BA
BJJ BA

Reputation: 127

SQL - Can you filter by datatype?

I'm looking at a database of mobile numbers and am trying to find errors.

For example, mobile numbers begin with '04', so to test for errors, I can query:

SELECT DISTINCT Names.Phone_M FROM Names
WHERE Names.Phone_M NOT LIKE '04%'

In testing for errors, I noticed some of the data contains text. Is there a way of filtering the data so it only brings up entries than contain some kind of text as well as numbers? i.e. If an entry is '0400000000 - John', can I write a query that will pick this up without simple trial and error?

Thanks

Upvotes: 4

Views: 4563

Answers (2)

user7154703
user7154703

Reputation:

You can try ISNUMERIC () function

where ISNUMERIC (Names.Phone_M) = 1 -- should match valid numbers

where ISNUMERIC (Names.Phone_M) = 0 -- should match invalid numbers

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use PATINDEX to check if the phones have characters other than numbers in them.

[^0-9] finds any characters other than 0,1,2,3,4,5,6,7,8,9.

SELECT DISTINCT Phone_M 
FROM Names
WHERE PATINDEX('%[^0-9]%',Phone_M) > 0
AND Phone_M LIKE '04%'

Generalizing the above to find all phones which can have numbers as well as text, use 

SELECT DISTINCT Phone_M 
FROM Names
WHERE PATINDEX('%[^0-9]%',Phone_M) > 0
AND PATINDEX('%[0-9]%',Phone_M) > 0

Upvotes: 4

Related Questions