Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2003
    Posts
    122

    Unanswered: Format Telepone Number

    I am using a stored procedure to query data in a SQL backend. I have the telephone number stored as 9999999999. I am wondering how I can get that number formatted and displayed in a list box (Access 2000). I want standard formatting, (999) 999-9999. How can I do this? Thanks.

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    create function dbo.fn_FormatUSPhone (
    @PhoneString varchar(10) ) returns char(14)
    as begin
    declare @RetVal char(14), @Area char(3), @Prefix char(3), @Nbr char(4)
    if isnumeric(@PhoneString) = 0
    set @PhoneString = replicate('0', 10)
    if datalength(@PhoneString) < 10
    set @PhoneString = replicate('0', 10-datalength(@PhoneString))+@PhoneString
    set @Area = cast(@PhoneString as char(3))
    set @Prefix = substring(@PhoneString, 4, 3)
    set @Nbr = reverse(cast(reverse(@PhoneString) as char(4)))
    set @RetVal = '(' + @Area + ') ' + @Prefix + '-' + @Nbr
    return @RetVal
    end
    go
    select field1, field2, dbo.fn_FormatUSPhone(phone_number_field) from your_table

  3. #3
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    If you are sure your table inserts are 10 digits all the time

    select '('+left(phone,3)+') '+substring(phone,4,3)+'-'+right(phone,4) from PhoneBook

    This will return exactly the same result as the last reply

  4. #4
    Join Date
    Sep 2003
    Posts
    122
    Thank you very much. That's got it.

  5. #5
    Join Date
    Sep 2003
    Posts
    122
    Okay, I thought that had it. I would like to store my telephone numbers as datatype bigint, but now the code no longer works. Apparently you cannot use the substring function on a bigint datatype. Is it possible to use a bigint datatype and still have the format I am looking for. Please help one more time.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do NOT store your phone number as bigint. Are you going to add phone numbers? Are you going to divide phone numbers? Are you going to average phone numbers?

    Phone numbers are character data that happen to be numerals.

    blindman

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    yeah, bigint is kinda odd

  8. #8
    Join Date
    Sep 2003
    Posts
    122
    I assume then that Social Security Numbers should also be stored as varchar?

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    you betchya, just set up check constraints with [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] and you'll be all set.

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Actually, there are more consideration to choose the one or the other datatype.

    Most importantly, your data type is part of your data model, especially your integrity rules. If in your model your telephone number are ten digits, you may use bigint, char(10) or three smallints; none of them are fitting your requirement completely, but you will have to add some check constraints like the SQL_DBA showed for char(10) or < 10000000000 and >= 1000000000 for bigint.

    When you ensured that your data is correct, there is also the aspect of usage. Than numeric operators are not applicable, isn't really an argument not to choose a numeric data type. However, the possibility of proper formatting may be an reason to choose the char(10) option. Another aspect is storage, in some case it may be important that bigint needs 2 bytes less than char(10). An index will also be smaller, and maybe the engine will also faster compare two numbers than two strings.

    However, I was wondering whether you model is fine. Is the part (999) your net number? In this case, you model would be correcter if you could ensure that you only have valid net numbers.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use bigint, and you will eventually wish you hadn't.

    You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.

    blindman

  12. #12
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by blindman
    Use bigint, and you will eventually wish you hadn't.

    You do not perform numeric operations on phone numbers or social security numbers, so it makes no sense to impose the restrictions of a numeric datatype while simultaneously forcing you to recast the values for string manipulation.

    blindman
    As I tried to explain, this is just one aspect. Optimized for presentation, you would even split the telefone number into three alphanumeric portions. Sematically, however, I would propose to model a separate net number, don't you agree?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I can see a reason for splitting off the net number (I think you are referring to "area code" in U.S.) into a separate field in some applications. To handle all possible international phone formats, I'm not sure if it could be broken apart in a consistent and reliable manner.

    blindman

  14. #14
    Join Date
    Sep 2003
    Posts
    122
    The advantage of using varchar outweighs the space savings in using bigint? That was my only question. I know all telephone numbers will be stored as 9999999999, so I thought I should use bigint and enjoy the space savings. Regardless, noone has posted indicating I can format the bigint number as (999) 999-9999, so I am stuck with varchar either way. Is that correct?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can format it that way after you cast your bigint value as char.

    '(' + Left(cast(YourValue as varchar(10)), 3) + ') ' + Mid(cast(YourValue as varchar(10), 4, 3) + '-' + right(cast(YourValue as varchar(10), 4)

    It is not a matter of can, but a matter of should. Any performance boost or space-savings you get from using bigint will be offset by added processing and administration time.

    blindman

Posting Permissions

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