MySQL selecting string with multi special characters

Question

I'm having a problem selecting strings from database. The problem is if you have +(123)-4 56-7 in row and if you are searching with a string 1234567 it wouldn't find any results. Any suggestions?

Answer 1

You can use the REPLACE() method to remove special characters in mysql, don't know if it's very efficient though. But it should work.

Answer 2

Query using replace function as -

select * from phoneTable where replace(replace(replace(phone, '+', ''), '-', ''), ')', '(') LIKE '%123%'

Answer 3

There is already another thread in SO which covers a very similar question, see here.

If it is always this kind of pattern you're searching, and your table is rather large, I advice against REPLACE() or REGEX() - which ofc will do the job if tweaked properly.

Better add a column with the plain phone numbers, which doesn't contain any formatting character data at all - or even better, a hash of the phone numbers. This way, you could add an index to the new column and search against this. From a database perspective, this is much easier, and much faster.

Answer 4

You can use User Defined Function to get Numeric values from string.

CREATE FUNCTION GetNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE FUNCTION GetNumeric (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
 END;

Then

Select columns from table
where GetNumeric(phonenumber) like %1234567%;