SQL - Strip non-numeric characters

[mystifier]

The following code is used to strip non-numeric characters from a very large table:

 

$prep1 = @mysql_query("update contacts set phone =  replace(phone, ' ', '')");

$prep2 = @mysql_query("update contacts set phone =  replace(phone, '-', '')");

$prep3 = @mysql_query("update contacts set phone =  replace(phone, '(', '')");

$prep4 = @mysql_query("update contacts set phone =  replace(phone, '), '')");

 

Can this be done in a single query?

 

 

[FuThAr]

i think you can use a single UPDATE query, with a CASE/WHEN/THEN statement (for each char), and the REGEXP operator:

UPDATE contacts 
SET phone = ( 
CASE  
WHEN phone REGEXP '.*-.*'
THEN replace(phone,'-','') 
ELSE phone 
WHEN [...]
THEN [...]
ELSE [...]
END); 

[Mchl]

Please do not duplicate topics.

 

http://www.phpfreaks.com/forums/index.php/topic,251919.msg1182938.html#msg1182938

[FuThAr]

sorry for the reply.

Alberto

[Mchl]

sorry for the reply.

Alberto

 

No need to be sorry. You don't have to track all mystifier's topics. He on the other hand, should do so.