• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1661
  • Last Modified:

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!

0
llputney
Asked:
llputney
  • 4
1 Solution
 
derekkrommCommented:
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)
0
 
llputneyAuthor Commented:
i feel like im having a "duh" moment...thanks for the help!
0
 
llputneyAuthor Commented:
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?
0
 
llputneyAuthor Commented:
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
0
 
llputneyAuthor Commented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now