user3404390 user3404390 - 1 year ago 37
SQL Question

SQL Query that checks only numeric characters in the database

I am working on a legacy system a client has. Phone numbers are stored in a multitude of ways. Ex:


The client wants to be able to search the database by phone number.

How could this be achieved without normalizing the data stored in the database? Is this possible?

I am wondering if it is possible to have a query that looks like:

SELECT FROM table WHERE phonenumber LIKE %input%

but that takes into account only the numerical characters in the db?

Answer Source

The approach I would take with this (i.e. not having a "normalized" value with only digits available, and a restriction of not adding an additional column with the normalized value...)

I would take the user input for the search, and add wild cards in strategic locations. For example, if the user provides search input of 3155551212), then I'd run a query that has a predicate equivalent to this:

  phonenumber LIKE '%315%555%1212%'

But if I'm not guaranteed that the provided search digits will be a full three digit area code, a three digit exchange (central office) code, and a four digit line number, for a broader search, I'd add wild cards between all of the provided digits, e.g.

  phonenumber LIKE '%3%1%5%5%5%5%1%2%1%2%'

This latter approach is less than ideal, because it could potentially provide more matches than aren't intended. Especially if the user is providing fewer than ten digits. For example, consider a phonenumber value:

  '+1 (315) 555-7172 ext. 123'

As a demonstration:

SELECT '+1 (315) 555-7172 ext. 123' LIKE '%3%1%5%5%5%5%1%2%1%2%'
     , '+1 (315) 555-7172 ext. 123' LIKE '%315%555%1212%'

There's no builtin string function in MySQL that will extract the digit characters from a string.

If you want a function that does that, e.g.

 SELECT only_digits_from('+1 (315) 555-7172 ext. 123')

to return


You'd have to create a stored function that does that. I wouldn't attempt doing it inline in the SQL statement, that would require an atrociously long and ugly expression.