BJJ BA BJJ BA - 13 days ago 7
SQL Question

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

Answer

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