MySQL : Problem in Updating a column for condition

I have one phone field column which contains phone numbers like that

'123456789'

'123-456-789'

etc

means it contain 9 digit number or number + hyphen.

I want to make a SQL query which updates all records in 'xxx-xxx-xxx' format. I have made few attempts but cannot get exact solution.

Please any one help me. Thanks in advanc开发者_开发技巧e.....

use something like

UPDATE mytable SET phone =  
CONCAT(SUBSTRING(phone, 1, 3),'-',SUBSTRING(phone, 4, 3),'-',SUBSTRING(phone, 7, 3))  

Also to only get the rows that are missing hyphens you would say WHERE phone not like '%-%'

Close. You'd first need to test if the string contained a '-' before adding more, but that's the right track.

UPDATE mytable SET phone = CONCAT(SUBSTRING(phone, 1, 3),'-',SUBSTRING(phone, 4, 3),'-',SUBSTRING(phone, 7, 4)) where INSTR( phone, '-' ) = 0;