SQL Server 2005- Add area code to records shorter than 10 characters

Question

Can anyone come up with a SQL routine or ideas to systematically append an area code to the beginning of every field in our SQL Server 2005 database that does not have an area code in it?

Perhaps someone could tell us how to return only rows that are less than 10 characters long and how to append the 3 digit area code to the beginning of each field string?

Thanks!

Answer 1

DECLARE @AreaCode char(3)
SET @AreaCode = 'XXX'

UPDATE myTable
SET myField = @AreaCode + myField
WHERE LEN(myField) < 10
AND myField NOT LIKE @AreaCode + '%'

This will work with SQL Server.

Answer 2

Ideally, the area code would be a separate column in your database. Since it's not designed that way though:

UPDATE
     PhoneNumbers
SET
     phone_number = '123' + phone_number
WHERE
     LEN(phone_number) = 7

Of course, if people have formatted the phone numbers then this won't work. I would need to know more about the data that's in your table. You might have to do something like:

UPDATE
     PhoneNumbers
SET
     phone_number = '(123) ' + phone_number
WHERE
     LEN(REPLACE(REPLACE(REPLACE(REPLACE(phone_number, '-', ''), '(', ''), ')', ''), '.', '') = 7

If people typed in stuff like "ext. 7" then that would further complicate things. See why it's better to break out a phone number instead of just having one column? ;)