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

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
0
jamesmetcalf74
Asked:
jamesmetcalf74
  • 4
  • 2
  • 2
  • +1
4 Solutions
 
_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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
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
 
_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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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