Phone number data formatting all over the place in my database

I have a table that has a column for phone numbers.  there are thousands in here and their formatting is not consistent.

ie....
 (999) 999-9999
1111111111
222-222-2222


they are all over the place
I need to update this to one format.     888-888-8888  example

does someone know how to do that?
Thanks a bunch
jamesmetcalf74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Does it contain only US phone numbers or mix of US and international numbers?

Do you need to support international numbers?
0
Martin MillerCTOCommented:
Good Question asked above if it's just USA phone.

For just 10 digits, e.g. 1234567890 and you have the fields with just the numbers...
to reformat with 123-456-7890

UPDATE PhoneNumTable
SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + '-' + 
                  SUBSTRING(PhoneNumber, 4, 3) + '-' + 
                  SUBSTRING(PhoneNumber, 7, 4)

Open in new window

1
_agx_Commented:
.... in addition, IF this is for handling US phone numbers only, you'd also need to

1. Remove any non-numeric characters first. One way is  with a custom UDF.  
2. Handle values with a leading area code, "1"  
3. Skip outliers like phone numbers with < 10 characters or > 11 characters

The query below would format any 10/11 digit phone numbers. As with any mass update, always backup the table first.

;WITH src AS
(   SELECT phoneID
            , phoneNumber
            , dbo.[fnRemoveNonNumericCharacters](phoneNumber) AS NumericPhone
    FROM yourTable
)
UPDATE  src
SET     src.phoneNumber = 
              CASE WHEN LEN(NumericPhone) = 10 THEN LEFT(NumericPhone, 3) +'-'+ SUBSTRING(NumericPhone,4,3) +'-'+ RIGHT(NumericPhone, 4)
			     WHEN LEN(NumericPhone) = 11 THEN SUBSTRING(NumericPhone,2,3) +'-'+ SUBSTRING(NumericPhone,4,3) +'-'+ RIGHT(NumericPhone, 4)
			     ELSE phoneNumber 
			  END
WHERE  LEN(src.NumericPhone) BETWEEN 10 AND 11	

Open in new window


You could also just store the raw numbers and do any formatting on the front end (my preference).

Finally, whichever method you choose, going forward, any formatting (or removal of non-numeric characters) should really be performed when the values are INSERT'ed, rather than after the fact.
1
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jamesmetcalf74Author Commented:
it is only for USA numbers
0
Martin MillerCTOCommented:
Great, USA 10 digit numbers only.  The +1 may be optional as an 11th digit. Between the solutions above, do you need any more assistance?
0
_agx_Commented:
Agreed.  Small correction to the 2nd example for 11 digit numbers:

;WITH src AS
(   SELECT phoneID
            , phoneNumber
            , dbo.[fnRemoveNonNumericCharacters](phoneNumber) AS NumericPhone
    FROM yourTable
)
UPDATE  src
SET     src.phoneNumber = 
              CASE WHEN LEN(NumericPhone) = 10 THEN LEFT(NumericPhone, 3) +'-'+ SUBSTRING(NumericPhone,4,3) +'-'+ RIGHT(NumericPhone, 4)
			     WHEN LEN(NumericPhone) = 11 THEN SUBSTRING(NumericPhone,2,3) +'-'+ SUBSTRING(NumericPhone,5,3) +'-'+ RIGHT(NumericPhone, 4)
			     ELSE phoneNumber 
			  END
WHERE  LEN(src.NumericPhone) BETWEEN 10 AND 11	

Open in new window

0
Scott PletcherSenior DBACommented:
I'd suggest not storing ANY formatting chars.  It's a waste of space and reduces flexibility.  You can keep a pattern of what the original format was. if you need to or would like to.  This would also allow you to format each output differently, based on a format saved in the row, if that might prove useful later (such as for international numbers, local numbers with extensions, numbers which use letters/words "800-FLOWERS", etc.).

It's easy enough to STUFF in the necessary formatting chars at run time, contingent on phone number length.  For example:

SELECT *, STUFF(CASE WHEN LEN(phone#) = 10 THEN STUFF(phone#, 7, 0, '-') ELSE phone# END, 4, 0, '-') AS phone#_formatted
FROM (
    VALUES('1234567890'),('1234567')
) AS test_data(phone#)

Then, if next week you have to change the output format to:
(123)-456-7890
You don't have to update the entire table again, just change the view and/or INLINE table-valued function that adds the formatting.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
_agx_Commented:
>> I'd suggest not storing ANY formatting chars.
I second that.  Like I mentioned earlier, my preference is to do formatting on the front end, or as Scott said when needed.
0
jamesmetcalf74Author Commented:
PerfectoMundo
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.