MySQL Regexp where like value

Question

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

It would be best if you have a column in the database for the cleaned-up version; even if you manage to sanitize on-the-fly, you lose any chance you had of using an index. — Amadan, Jul 17 '13 at 06:59
you can match the string from right 9 chars with REPLACE(val, ' ' ,'') — DevZer0, Jul 17 '13 at 07:00

Jay Harris · Answer 1 · 2013-07-17T07:26:03.533

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

score 1 · Answer 2 · answered Jul 17 '13 at 07:04

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);

score 0 · Answer 3 · answered Jul 17 '13 at 07:07

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;

score 0 · Answer 4 · edited Jul 17 '13 at 07:21

0

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...

edited Jul 17 '13 at 07:21

mishik

9,973
9
45
67

answered Jul 17 '13 at 07:15

AKDADEVIL

206
1
7

MySQL Regexp where like value

4 Answers4