I am working on a legacy system a client has. Phone numbers are stored in a multitude of ways. Ex:
SELECT FROM table WHERE phonenumber LIKE %input%
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')
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.