SQL - Can you filter by datatype?

Question

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

Answer 1

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