I am trying to figure out a way where I can select a phone number that might be used across many regular expression formats...

For example: 555-444-3333 4344443434 might 2 rows in my db

I want to select a phone number where the number I pass in matches both the above numbers (using a regular expression). I am using node.js. Is this possible?

CodePudding user response：

You could use REGEXP here:

SELECT *
FROM yourTable
WHERE phone_num REGEXP '^[0-9]{3}-?[0-9]{3}-?[0-9]{4}$';

CodePudding user response：

This problem is surprisingly hard to solve well. You have to cope with punctuation and country codes. Here's how I have done it.

• Use three different columns to store versions of your phone_num.

  • One will contain precisely the input given to you by your user or data source. For example, '1 (978) 555-1212'.
  • The second, call it phone_num_clean, has the same information but with everything except digits removed. For example '19785551212'. Note well: this must be a VARCHAR(32) data type, not any kind of number.
  • The third, call it phone_num_reversed, has the same information as the second, but in reverse order. For example '21215558791'.

  Put indexes on the second and third columns.

• Then, when your user searches for a phone number, your application strips out the punctuation (converting the first form above to the second). That stripped form gets used in a query like this ... with the placeholder called ?num?.

  SELECT phone_num FROM your_table 
    WHERE (
               phone_num_clean LIKE CONCAT(?num?, ''%')
            OR phone_num_reversed LIKE CONCAT(
                  REVERSE(RIGHT(?num?, LENGTH(?num?) - 3)), '%')
          )
  

  This works by comparing the leading and trailing digits of the number. The RIGHT() function call attempts to remove any country code from the user's input.

• Then if you get multiple results from this, show the results to users and ask them to choose the right number. This approach is really good for autocomplete.

And, be sure to read this. Falsehoods Programmers Believe About Telephone Numbers.