MySQL REGEXP - removing spaces and non-numeric characters

Question

I want to search the MySQL database for phone numbers.

Currently with this request:

SELECT person FROM people WHERE phone_number RLIKE '123456789'

He will not find:

(123) 456 789
(12) 456789
123 456 789
etc etc

In my MySQL query, can I strip everything and just leave numbers?

I would also like to look for the removal of all spaces and just leave numbers and characters, as well as delete all characters and just leave numbers and spaces. Not sure how easy it all is.

Appreciate your help!

Answer 1

What about:

SELECT
    person,
    replace(replace(replace(replace(phone_number,' ',''),'(',''),')',''),'-','') as phone_number
FROM
    people
WHERE
    phone_number RLIKE '^[+]?[-() 0-9]+$';

matches numbers starting with a plus sign; they can contain hyphens, brackets, and spaces. but there are no plus signs, except at the beginning. as well as no characters. also removes hyphens, spaces and brackets.

Answer 2

, , .

: regex RLIKE, :

SELECT person 
 FROM people
 WHERE phone_number RLIKE '[() ]*1[() ]*2[() ]*3[() ]*4[() ]*5[() ]*6[() ]*7[() ]*8[() ]*9'

, . . mySQL (, php), , "123456789" "x1x2x3x4x5x6x7x8x9", "x" - [() ]*.

-: mysql REPLACE :

SELECT person 
 FROM people
 WHERE REPLACE(REPLACE(REPLACE(phone_number,' ',''),'(',''),')','') = '123456789';

, phone_number '..

, . , REPLACE(phone_number,' ',''), () REPLACE.

, . MySQL REPLACE!

Answer 3

I made a function that returns only all numbers. then I called the function through

SELECT person FROM people WHERE returnNumericOnly(phone_number) = '123456789'

my function is this:

DELIMITER $$

USE [tablename]$$

DROP FUNCTION IF EXISTS `returnNumericOnly`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `returnNumericOnly`(str VARCHAR(1000)) RETURNS VARCHAR(1000) CHARSET latin1
DETERMINISTIC
BEGIN
  DECLARE counter INT DEFAULT 0;
  DECLARE strLength INT DEFAULT 0;
  DECLARE strChar VARCHAR(1000) DEFAULT '' ;
  DECLARE retVal VARCHAR(1000) DEFAULT '';

  SET strLength = LENGTH(str);

  WHILE strLength > 0 DO
    SET counter = counter+1;
    SET strChar = SUBSTRING(str,counter,1);
    IF strChar REGEXP('[0-9]+') = 1
      THEN SET retVal = CONCAT(retVal,strChar);
    END IF;
    SET strLength = strLength -1;
    SET strChar = NULL;
  END WHILE;
RETURN retVal;
END $$

DELIMITER ;

Answer 4

try to get around this. This is not good, but it will give you what you want.

SELECT temp.person, p.phone_number
  FROM people p
 INNER JOIN (SELECT REPLACE(REPLACE(REPLACE(phone_number,' ',''),'(',''),')','') phone_number,id
              FROM test) temp
   ON p.id=temp.id -- primary key of the table
 WHERE temp.phone_number='123456789';