MySQL 5.7 Compare only numbers from a mixed VARCHAR column

Question

NOTE: Please note this is NOT the same as similarly titled questions (see "What I've Tried", below)

Background:

I need to search a database string column for a phone number that matches a given search criteria.

The database column is a varchar with various user supplied additional (ie non-numeric) characters.

My original idea was to use a system to convert the column string to a numeric only format (in PHP this would be via PCRE functions) and then do a straight indentical comparison

Example data held:

id        telephone:
----------------------
 1     '01576 456 567'
 2     '07768345998'
 3     '+447588 43 34 56'
 4     '01524-901-335'

There are a variety of human readable formats held, these are submitted by the end user and are often historic.

Problem:

I can not find a way to search this column for a number. I have stripped down the search field in PHP to number only (0-9). I want to try something like:

"Search the telephone column and find where the numeric ONLY value (from a mixed string) matches the given search string exactly.

(pseudo-code:)

   SELECT telephone, id FROM phones WHERE 
          REGEX_REPLACE(`telephone`, '[^0-9]') = :searchNumber 

(:searchNumber is the PDO placeholder.)

Example Search Term:

"01576456567"

Expected output:

From entering the search term into the SQL query I want to be able to retrieve the id number. In the above search example; $result['id'] = 1;

Limitations:

This is on MySQL vesion 5.7 only. I can not use MySQL 8.0 here.

It would cause a lot of secondary work to convert the phone columns to numeric column types and we don't have the time flexibility to do this right now.

What I've Tried:

• The REGEXP type functions on MYSQL return true/false (0/1) rather than a REGEXP processed output string.

• CASE to SIGNED/UNSIGNED does not work because it breaks at any whitespace in the string and also can lop off the leading zero.

• I have read various MySQL Stack Overflow answers

Escape Clause:

If my query is confusing this PHP code may better example what I'm trying to achieve.

• If all else fails I can export all of the numbers and run them through a PHP loop which would do the same thing:

  $searchNumber = preg_replace('/[^0-9]/','',$searchNumberSource);
  foreach ($numberFromDb as $row){
       if(preg_replace('/[^0-9]/',''.$row) === $searchNumberSource){
           // Matching number is found.
           break;
       }
  }
  

Answer 1

You should fix the data so it matches how you want to use it. That may take some effort now, but fixing the data will simplify your code ongoing -- rather than having arduous work-arounds.

My suggestion is to make the change on the application side. Change the number to a regular expression:

SELECT telephone, id
FROM phones
WHERE telephone REGEXP :searchNumber_regex;

Then :searchNumber_regex for '"01576456567' would look like:

'^[^0-9]*0[^0-9]*1[^0-9]*5[^0-9]*7[^0-9]*6[^0-9]*4[^0-9]*5[^0-9]*6[^0-9]*5[^0-9]*6[^0-9]*7[^0-9]*$'

Basically, the pattern [^0-9]* is at the beginning and end and in-between every number.

Answer 2

A trivial way is a nested use of replace

select replace(replace(replace(telephone,' ',''), '-',''), '+','')