How to replace non-numiric characters from a string MySQL

Question

I need a better way to replace a non-numeric characters in a string.

I have phone numbers like so (888) 488-6655 888-555-8888 blah blah blah

So I am able to return a clean string by using a simple replace function but I am looking for a better way may be using expression function to replace any non-numeric value. like space slash, backslash, quote..... any none numeric value

this is my current query

SELECT
a.account_id,
REPLACE(REPLACE(REPLACE(REPLACE(t.phone_number, '-', ''), ' ', ''), ')', ''),'(','') AS contact_number,
IFNULL(t.ext, '') AS extention,
CASE WHEN EXISTS (SELECT number_id FROM contact_numbers WHERE main_number = 1 AND account_id = a.account_id) THEN 0 ELSE 1 END AS main_number,
'2' AS created_by
FROM cvsnumbers t
INNER JOIN accounts a ON a.company_code = t.company_code
WHERE REPLACE(REPLACE(REPLACE(REPLACE(t.phone_number, '-', ''), ' ', ''), ')', ''),'(','')  NOT IN(SELECT contact_number FROM contact_numbers WHERE account_id = a.account_id)
AND LENGTH(REPLACE(REPLACE(REPLACE(REPLACE(t.phone_number, '-', ''), ' ', ''), ')', ''),'(','') ) = 10

How can I change my query to use an REGEX to replace non-numeric values.

Thanks

As all those other questions containing mysql & regex indicate, that's not in native MySQL. You could use this. — Wrikken
– Wrikken, Commented May 15, 2013 at 18:20

Gordon Linoff · Accepted Answer · 2013-05-15 19:10:45Z

1

This is a brute force approach.

The idea is to create a numbers table, which will index each digit in the phone number. Keep the digit if it is a number and then group them together. Here is how it would work:

select t.phone_number,
       group_concat(SUBSTRING(t.phone_number, n.n, 1) separator '' order by n
                   ) as NumbersOnly
from cvsnumbers t cross join
     (select 1 as n union all select 2 union all select 3
     ) n
where SUBSTRING(t.phone_number, n.n, 1) between '0' and '9'
group by t.phone_number;

This example only looks at the first 3 digits in the number. You would expand the subquery for n to the maximum length of a phone number.

answered May 15, 2013 at 19:10

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joanna Derks · Accepted Answer · 2013-05-15 18:35:30Z

0

I don't know the mySql regex flavour but I would give this a go:

REPLACE(t.phone_number, '[^\d]+', '')

[^\d]+ means: 'Match everything that is not a digit, once or more times'

You might need to escape the backslash ([^\\d]+).

answered May 15, 2013 at 18:35

Joanna Derks

4,0833 gold badges28 silver badges32 bronze badges

2 Comments

B8vrede Over a year ago

In MySQL it is not possible to replace in the output using Regex, not without 3rd party code. But your Regex is correct :)

Mike Over a year ago

I thought it worked but it is not working. It does not replace all characters from my string

Collectives™ on Stack Overflow

How to replace non-numiric characters from a string MySQL

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related