MySQL Regexp在哪里价值[重复]

I am trying to match a numeric value that is purely numbers to another value in my database that is the same numbers but has symbols etc (it's phone numbers).

How can I get MySQL to only match the regexp of the mainNumber column (which has symbols etc in it) to my value (just numbers)?

Below is my code:

$SQL = "SELECT entities.name
        FROM entities_main_numbers
           LEFT JOIN entities ON entities.id = entities_main_numbers.entityId
        WHERE mainNumber = ?
        LIMIT 1";

E.g. +44 (0)800 123 123 to match against 440800123123

</div>

You can be creative and match the 9 digits from the right after getting rid of the spaces, but if the full number is needed you can place few more recursive calls to REPLACE eliminating the () and +

mainnumber = RIGHT(REPLACE('+44 (0)800 123 123', ' ', ''), 9);

MYSQL SOLUTION

you can use mulitple replace statements

 WHERE REPLACE(REPLACE(REPLACE(REPLACE(mainNumber, '+', ''), ')', ''), '(', ''), ' ', '') = $targetNum

very ugly but it works. replaces ('+', ' ', '(', ')') with ''

this will convert this number +44 (0)800 123 123 to this number 440800123123 making your statement true if $targetNum = 440800123123

OR PHP SOLUTION

PHP

 $targetNum = preg_replace('/(\d\d)(\d)(\d\d\d)(\d\d\d)(\d\d\d)/', '+$1 ($2)$3 $4 $5', $targetNum);

 // will convert 440800123123 to +44 (0)800 123 123
 // essentially matching the way mainNumber is stored

SQL

 WHERE mainNumber = $targetNum

might be a better solution because it's less taxing on the database

very ugly, but if you just want to use built in functions, you can use a chain of REPLACE to filter out any characters you dont want.
eg:

SELECT REPLACE(
  REPLACE(
    REPLACE(
      REPLACE(
        entities.name," ","")
      ,"+","")
    ,"(","")
  ,")","") FROM entities_main_numbers;

I'm not an MySQL-expert, but you could probably create a function that takes the numeric value. e.g. '440800123123' and transforms it into a regex like

'\D*4\D*4\D*0\D*8\D*0\D*0\D*1\D*2\D*3\D*1\D*2\D*3\D*'

(\D <=> [^0-9] in case this is not defined in MySQL)

This you could use with the normal REGEXP-command...