llputney
asked on
SQL Update > Add Area Code to Pre-existing Phone Numbers
I have 1000 records in a customer table where it's missing an area code in the phone number. Is there an easy update statement with substrings to automatically add an area code to all phone numbers that do not currently have an area code?
I tried something like this and it does not work, but this is to give you an idea of what I'm trying to do.
UPDATE tedp_consumer
SET home_phone = SUBSTRING(home_phone, 1, 1) + '605-' + SUBSTRING(home_phone, 4, 3) + '-' + SUBSTRING(home_phone, 7, 4)
Your help is greatly appreciated, thanks!
I tried something like this and it does not work, but this is to give you an idea of what I'm trying to do.
UPDATE tedp_consumer
SET home_phone = SUBSTRING(home_phone, 1, 1) + '605-' + SUBSTRING(home_phone, 4, 3) + '-' + SUBSTRING(home_phone, 7, 4)
Your help is greatly appreciated, thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oops...one more question
If I do a select statement like
select home_phone from tedp_consumer
WHERE len(home_phone=8)
I get an error saying "Incorrect syntax near '=' ... how do I work around this?
If I do a select statement like
select home_phone from tedp_consumer
WHERE len(home_phone=8)
I get an error saying "Incorrect syntax near '=' ... how do I work around this?
ASKER
uhm...that didnt quite work....when i first tried it, i did it for a single number "WHERE id = xxxx"....when I tried, "WHERE len(home_phone=8)" i got an error
ASKER
Nevermind...the correction is
if formatted as 'xxx-xxxx'
UPDATE tedp_consumer
SET home_phone = '605-' + home_phone
WHERE len(home_phone)=8
if formatted as 'xxxxxxx'
UPDATE tedp_consumer
SET home_phone = '605-' + left(home_phone,3) + '-' + right(home_phone,4)
WHERE len(home_phone)=7
if formatted as 'xxx-xxxx'
UPDATE tedp_consumer
SET home_phone = '605-' + home_phone
WHERE len(home_phone)=8
if formatted as 'xxxxxxx'
UPDATE tedp_consumer
SET home_phone = '605-' + left(home_phone,3) + '-' + right(home_phone,4)
WHERE len(home_phone)=7
ASKER