CODEWITHSUNDEEP
mysql
Ben Sinclair
Ben Sinclair

Reputation: 3986

MySQL REGEXP - Removing white space and non-numeric characters

I want to do a search in a MySQL database for phone numbers.

At present with this query:

SELECT person FROM people WHERE phone_number RLIKE '123456789'

It wont find:

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

In my MySQL query is it possible to strip everything and just leave the numbers?

I'd also like to search removing all spaces and just leaving the numbers and characters and also removing all characters and just leaving numbers and spaces. Not sure how easy all this is.

Appreciate your help!

Upvotes: 5

Views: 15817

Answers (4)

lenne
lenne

Reputation: 83

i did a function that returns all numbers only.. then i called the function by

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

my function is like 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 ;

Upvotes: 5

davogotland
davogotland

Reputation: 2777

how about:

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

matches numbers that start with a plus sign, they may contain hyphens, parenthesis and spaces. but no plus signs other than at the start. and also no characters. also removes hyphens, spaces and parenthesis.

Upvotes: 8

Bryan
Bryan

Reputation: 1241

try to work around like this. It's 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';

Upvotes: 1

mathematical.coffee
mathematical.coffee

Reputation: 56905

I can think of two ways to do it, neither as elegant as I'd hoped though.

First: Use regex in RLIKE to allow spaces and brackets everywhere:

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

It basically says there can be brackets and spaces in between each number. It's a bit awkward to make the string though. If you are calling mySQL from another language (say php) you could use the other language to turn '123456789' into 'x1x2x3x4x5x6x7x8x9' where 'x' is [() ]*.

Second: use mysql REPLACE to remove spaces and brackets before doing comparison:

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

This successively replaces spaces, open brackets, and closing brackets in phone_number with ''.

I think I prefer this second method. To remove all spaces you just need REPLACE(phone_number,' ','') and to remove all () you need two REPLACE.

I suppose the first is more flexible with respect to adding extra characters though. It's a shame MySQL doesn't have a regex-enabled REPLACE!

Upvotes: 4

Related Questions