CODEWITHSUNDEEP
mysql
Sachin R
Sachin R

Reputation: 11876

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 advance.....

Upvotes: 0

Views: 235

Answers (2)

Haim Evgi
Haim Evgi

Reputation: 125564

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 '%-%'

Upvotes: 2

MonkeyWrench
MonkeyWrench

Reputation: 1839

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;

Upvotes: 0

Related Questions