[SOLVED] 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 anyone supply a single-pass solution?

 

[gevans]

My only thought is to put it all into one line (but still run four queries as replace() will only accept a string, not an array of variables to replace;

 

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

[Carth]

If this data is already in the database, then it doesn't matter so much. The script to correct it only needs to be run once, even if it's slow or needs multiple passes.

 

From then on only insert the data if it's in the correct format. Do any string modification in PHP beforehand - and there are more efficient ways to do it in PHP than in SQL.

[Mchl]

geavns: mysql_query only support single queries

 

mystifier:

Something like

update contacts set phone =  replace(replace(replace(replace(phone, ')', ''), '(', ''), '-', ''), ' ', '')

would probably work... though it's not pretty...

 

[Adam]

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

[Mchl]

Oooo... nice

[fenway]

FYI, that's only guaranteed to work for single-table update statements.

[mystifier]

Thanks Guys,

 

I didn't think about trying to nest replaces which is nice but I think the mutiple set is the clearer way to go with my weaker than average brain.