Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Join Date
    Dec 2012
    Posts
    11

    Unanswered: Phone Number formatting hell

    I have a table filled with phone numbers entered in every different way possible (5551212, 1115551212, 111-555-1212, 111 555 1212, 111-555-1212 ex 1234, 5551212 x1234) and I would like to pull them from the table using sql 2008 and have the formatting look like this: (111)555-1212 x1234. People of the internet, you're my only hope! How can I make this happen? Where numbers are missing, I would prefer to leave the area blank, ie 5551212 becomes 555-1212, and 1115551212 becomes (111)555-1212, extension numbers only where they are currently included. Is there an easy fix for this at all?
    Last edited by JayGrrr; 02-12-13 at 13:33.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Answer: It depends.

    How many variations are there on extension? If they all have some form of character data between the phone number and the extension (x, ext, ex, extn, etc.), then this can probably be done. I would first strip out all the 'extra characters (space, hyphen, parentheses, periods, commas), and get all of the phone numbers in the form 1115551212. It will take a few tries to isolate all of these extra characters. After that, it cones down to the Length function. Anything with a length of 10 has an area code. 7 has no area code. If I have time, I will see if I can cook up a running example, but this may get you started toward a solution.

  3. #3
    Join Date
    Dec 2012
    Posts
    11
    It's hard to tell how many variations on extension.. There's over 23,000 records and very little consistency, so I think it's safe to say that if there is a different way of expressing ext, it's in there somewhere. I like your idea of breaking them down to numbers first, to normalize all the data, and then using the Length function. I'll see what I can do. Thank you

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are all of the numbers NANPA, or are some international? Are any of the numbers old (for this discussion, before 1997) so that they use different rules?

    I've seen cleanup packages, but I'd have to dig to find them. I'd definitely keep a copy of the original values if you decide to monkey with them.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Dec 2012
    Posts
    11
    Although I don't recognize any as international by scanning thru quickly, it is conceivable that there are a few that are Canadian or from Mexico. They would be few and far between tho, and I would be willing to sacrifice them to get get the rest of the #s up to par. Some of the numbers could definitely be pre 1997. And there are more than a few NULL values. You mentioned cleanup packages.. Is it safe to say there is no easy fix for this, such as a line or two of code I can throw in just to make a report look nice?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can start with this:
    Code:
    create function [dbo].[FormatPhone](@PhoneNum varchar(50))
    returns varchar(50)
    as
    -- FormatPhone
    -- blindman, 3/2004
    --  Formats a string of 7-10 numeric values as a phone number, with or without the area code.
    begin
    -- Test parameters
    -- declare	@PhoneNum varchar(50)
    -- set	@PhoneNum = '6142462473'
    declare	@ReverseNum varchar(50)
    set	@ReverseNum = Reverse(@PhoneNum)
    return	reverse(left(@ReverseNum, 4) + '-' + substring(@ReverseNum, 5, 3) + coalesce(' )' + nullif(substring(@ReverseNum, 8, 3), '') + '(', ''))
    end
    ...on the condition that you retain my comments and attribution, and that you also post any improvements you make!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...you also need this, need clean up the data before you format it:
    Code:
    CREATE function dbo.StripPhone(@PhoneNumber varchar(50))
    returns varchar(50)
    as
    -- StripPhone
    -- blindman, 3/04
    -- Strips a phone number of non-numeric characters and leading 1s.
    -- Test parameters
    -- declare	@PhoneNumber varchar(50)
    -- set	@PhoneNumber = '1-(301)-887-2403             '
    begin
    declare	@NewString varchar(50)
    declare @Counter int
    declare	@TestChar char(1)
    set	@NewString = ''
    set	@Counter = 1
    while	@Counter <= len(@PhoneNumber)
    	begin
    	set @TestChar = substring(@PhoneNumber, @Counter, 1)
    	if ASCII(@TestChar) between 48 and 57 set @NewString = @NewString + @TestChar
    	set @Counter = @Counter + 1
    	end
    while left(@NewString, 1) in ('1', '0') set @NewString = right(@NewString, len(@NewString)-1)
    return	nullif(@NewString, '')
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Dec 2012
    Posts
    11
    Oh boy.. This looks pretty serious. I'm a novice programmer, so I'm going to have to play w this a bit to figure out what values to enter where. Will this affect the table itself or just the results I'm pulling for my report?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Running these scripts in your database will create "User Defined Functions", which you can then call in your SQL like any other database function:

    Code:
    Select	StripPhone([YourPhoneValue]) as JustTheNumbers,
    		FormatPhone(StripPhone([YourPhoneValue])) as FormattedPhoneNumber
    from	[YourTable]
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As a rule of thumb, if the words "update", "insert" or "delete" show up in the text (not part of a comment), then you should worry. What blindman posted are user defined functions, which will format the data if invoked.

  11. #11
    Join Date
    Dec 2012
    Posts
    11
    Oh, I see. Ok. Thank you guys very much, I really appreciate the assistance!! You've given me a bunch of stuff to play with. Hopefully I'll be able to make it work. Thanks!!

  12. #12
    Join Date
    Dec 2012
    Posts
    11
    ANother question- When a function is created, is it sent somewhere and stored, like a stored procedure or a view, where I can call upon the function for future reports, or does it exist only in the application currently being run, where it does it's task and then disappears until that specific report runs again?

    ***Nevermind, found em!!*******

  13. #13
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I have a table filled with phone numbers entered in every different way possible (5551212, 1115551212, 111-555-1212, 111 555 1212, 111-555-1212 ex 1234, 5551212 x1234) and I would like to pull them from the table using SQL 2008 .. <<

    Find the guy that allowed this crap in the database; run over him in parking lot; re-write all his code. While this is not a technical answer, it is often a good solution to many problems.

    Data scrubbing is not a good job for SQL. Find an address formatting product like SAA or Melissa data and clean up the data with it. They will spot bad areas codes, etc.

    The column for phone numbers needs to be in the International E.164 format in a CHAR(15) column. Read (E.164 - Wikipedia, the free encyclopedia) for an overview.

    >> .. and have the formatting look like this: (111)555-1212 x1234.<<

    You are also making a classic design error. Do not do display formatting in the database. We are the database; we throw those 15 digits over the wall and the presentation layer reads the ISO country code from a second column or in the application to find the local display template. The internal switch board extension is not part of the phone number.

    Before you ask if keeping extra digits that have to be pruned most of the time is worth it, I will answer “yes” to that question. Want some horror stories about a company that bought a European operation?

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    You are also making a classic design error.
    No, he is not making a "classic design error".
    He is dealing with a classic design error, and asked for assistance. Joe, I do not understand what you persist in being unable to understand this. Are you this far removed from the task of solving real life problems?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    No, he is not making a "classic design error".
    Joe didn't explain what he meant very well, but I see this as a design error too.

    I think that a database ought to store data. Formatting needs to be done at the client, because one user or platform might format a phone number one way and another might format it a very different way. I don't think that formatting should be stored, I see formatting as a user choice at the point of display instead of that formatting being a fundamental part of the data.

    Joe has some "interesting" people skills and those definitely are not his strong suit. Joe does understand data, the best practices for dealing with it, and much more relational algebra and set theory than most people I've met. I don't always agree with him, but in the case of "not storing formatting information" I definitely do agree with him.

    I know that Joe irritates you... Strong personalities like yours and his often collide! I have to admit that I have problems with some of Joe's postings, but we're working on improving that!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •