Need to remove all '-' from rsTel datafield in database?!

[jarv]

hi,

 

in my database I have a rsTel datafield so most phone numbers read something like this: 0180-320-1401

 

I would like to remove the '-' and put a space after the first 5 characters is there an UPDATE SQL statement I can write or PHP code which will update ALL my phone numbers in my database?

[AbraCadaver]

hi,

 

in my database I have a rsTel datafield so most phone numbers read something like this: 0180-320-1401

 

I would like to remove the '-' and put a space after the first 5 characters is there an UPDATE SQL statement I can write or PHP code which will update ALL my phone numbers in my database?

 

I wouldn't worry about the space.  This will replace the - though:

 

UPDATE table_name SET datafield = REPLACE(datafield, '-', '')

 

If you must have the space, then look at the string functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html.  Probably a combination of LEFT, LENGTH, RIGHT and CONCAT/CONCAT_WS.  Should be a fun learning exercise :-)

[AbraCadaver]

Or just SUBSTRING and CONCAT_WS.

[jarv]

Thankyou very much indeed AbraCadaver!!!! x