CODEWITHSUNDEEP
sqlmysqldatabase
Tauren
Tauren

Reputation: 27235

SQL to return field with non-numeric characters removed in MySQL

I have a MySQL table containing phone numbers that may be formatted in many different ways. For instance:

The field is stored as a string. All I want to do is return the field with anything non-numeric removed. What SQL could be used to do this in MySQL? So the values above would all return either 9999999999, 19999999999, or 019999999999.

Note this is for a query that will not be run regularly, so I don't need to worry about optimization, indexing, or anything else. However, I do need to include this into a fairly complex join with several other tables. At the most basic level, I'm attempting to find duplicate records and want to group by or at least order by phone number.

Upvotes: 2

Views: 4166

Answers (5)

VenerableAgents
VenerableAgents

Reputation: 656

I had this same problem! You can see how I solved it here.

Here's the functions I had to create and use that worked out great:

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

CREATE FUNCTION NumericOnly (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 = REPLACE(val,".","");
 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;

CREATE FUNCTION FormatPhone (val VARCHAR(255))
 RETURNS VARCHAR(255)
BEGIN
  SET val = RIGHT(CONCAT("0000000000",NumericOnly(val)),10);
  RETURN CONCAT("(",LEFT(val,3),") ",LEFT(RIGHT(val,7),3),"-",RIGHT(val,4));
END;

Use it like this to get your phone number formatted:

SELECT FormatPhone(PhoneNumberColumn) FROM TableName;

Or like this to only get the digits:

SELECT NumericOnly(PhoneNumberColumn) FROM TableName;

Upvotes: 0

potatopeelings
potatopeelings

Reputation: 41065

There's a UDF available for regex replacments (referred to in How to do a regular expression replace in MySQL?).

Or you could write your own UDF using a substring and ASCII code check.

Upvotes: 1

vava
vava

Reputation: 25371

This is the work for small regexp but unfortunately MySQL does not support regexp replace (unless you want to recompile it from sources). So I'd say do the cleaning on the code side, it'll be much easier.

Upvotes: 0

Your Common Sense
Your Common Sense

Reputation: 157863

You have to normalize these numbers before inserting them into database.
Make it stored in numbers only.
Otherwise it would be pain in the neck to get it right.

Upvotes: 2

Neil Knight
Neil Knight

Reputation: 48547

You could use the REPLACE command encapsulating the whole string with the different variations in order to remove them.

Upvotes: 0

Related Questions