user3404390 user3404390 - 3 months ago 7
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:

514-879-9989
514.989.2289
5147899287


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

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

 13155557172123

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.