none
UDF : Format UK phone numbers

    Question

  • Hi,

    I need to create a user defined function which will format UK phone numbers, cell numbers. The formatted phone numbers should be like +44 (0) 499 472001. I am not sure how the data will come to the user defined function but the formatted phone number should have prefix '+' followed by country code then '(0)'.

    What is the best way to implement

    Friday, July 22, 2011 12:08 PM

Answers

  • Another valid perspective is that it is better to do this kind of formatting in the front end application rather than in the database.

    Friday, July 22, 2011 12:50 PM
    Moderator
  • Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

    Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

    >> I need to create a user defined function which will format UK phone numbers, cell numbers. <<

    NO. Back to basics! Where does a tiered architecture do display formatting? Remember your second or third programming class? ANSWER: in the front end!

    How become a database programmer. Think “data” and not “display screens” and “printouts” Research the international standards http://en.wikipedia.org/wiki/E.164. Store the phone numbers as 15 digits. Google the regular expression for validating them. You are done, and your data has integrity.
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Friday, July 29, 2011 12:26 AM

All replies

  • Hello

    Welcome to the Transact SQL Forum.  Here are a few things to get you oriented.  First, give a look at these two guidelines for posting questions in the Transact SQL Forum:

    MSDN Transact SQL Forum Posting Guidelines:

       http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/af20783d-2489-46e4-b6f4-be0ab6d514cb

          Posted by Clifford Dibble

          http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/ce8989b5-dc58-47d8-af65-db5bcd4f7ee2

          Posted by Phil Brammer

    Next, become aware of the code and samples that you post.  One thing that will help you get better answers for your questions is to make your source code more readable.  Therefore, this forum provides a code formatting tool.  You can find the code tool.  The code tool is identified on the tool pallet by an icon that looks like a tiny window with the symbols “</>”.  Use this tool to insert your sample code; this will help the people that read your code.

    If you are getting some kind of error condition, please provide a full description of the error condition; if you are getting an error message from SQL Server itself, please provide the exact error message is generated.

    Realize that your question is incomplete because you haven't defined the input.  You need to provide for us a sample input to go with your sample output.  Nonetheless, if you are going to abstract this as a function it would be best for performance purposes that you create an inline table function for this.

    Now I realize that this is not intuitive, however for performance reasons you should usually try to avoid use of scalar functions.  I am going to assume for the moment and for argument's sake that your input is an integer.  For example:

    create function dbo.uk_Phone_Number( @phone_Number integer )
    returns table
    
    return
    ( select
      '+44 (0) '
       + str(@phone_Number / 1000000, 3)
       + ' ' + str(@phone_Number % 1000000, 6)
      as uk_Phone_Number
    ) 
    go
    
    declare @test table (phone_Number int)
    insert into @test select 499472001
    
    select
     uk_Phone_Number
    from @test
    cross apply dbo.uk_Phone_Number(phone_number) as phx
    /* -------- Output: --------
    uk_Phone_Number
    ------------------
    +44 (0) 499 472001
    */
    
    


    .


    Friday, July 22, 2011 12:31 PM
    Moderator
  • Hello Prashant,

    Since you do not know how the data will come, it is difficult to answer

    But as general, CLR functions enables t-sql developers use VB.NET or C# regular expressions, formatting options etc in SQL Server environment.

    You can think of creating a SQL CLR function

     

    Friday, July 22, 2011 12:39 PM
    Moderator
  • Another valid perspective is that it is better to do this kind of formatting in the front end application rather than in the database.

    Friday, July 22, 2011 12:50 PM
    Moderator
  • Dear Sir sir you can replace the sign "+" sign with "00"

     "+44 (0) 499 472001"  --------------->    "0044 (0) 499 472001"

     

    Regards

    Mona

    • Proposed as answer by mnasr Friday, July 22, 2011 2:03 PM
    • Unproposed as answer by Kalman TothModerator Thursday, July 28, 2011 9:41 PM
    Friday, July 22, 2011 2:03 PM
  • Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

    Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

    >> I need to create a user defined function which will format UK phone numbers, cell numbers. <<

    NO. Back to basics! Where does a tiered architecture do display formatting? Remember your second or third programming class? ANSWER: in the front end!

    How become a database programmer. Think “data” and not “display screens” and “printouts” Research the international standards http://en.wikipedia.org/wiki/E.164. Store the phone numbers as 15 digits. Google the regular expression for validating them. You are done, and your data has integrity.
    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Friday, July 29, 2011 12:26 AM
  •  

    >>  The formatted phone numbers should be like +44 (0) 499 472001. <<

    Absolutely NOT.

    Adding a (0) in the international format is NOT valid. This is specifically excluded by the ITU-T E.123 standard.

    The international format should include only the digits that must be dialled from abroad.

     

     

    The UK uses a range of number lengths and formats.

    NSN is National Significant Number. This is all of the digits after the 0 trunk code or +44 country code.

    This list shows them all (in national format):

     

    7 digit NSNs

    • 0800 1111
    • 0845 46 47

     

    9 digit NSNs

    • (016977) 2xxx
    • (016977) 3xxx
    • (01xxx) xxxxx
    • 0500 xxxxxx
    • 0800 xxxxxx

     

    10 digit NSNs

    • (013873) xxxxx
    • (015242) xxxxx
    • (015394) xxxxx
    • (015395) xxxxx
    • (015396) xxxxx
    • (016973) xxxxx
    • (016974) xxxxx
    • (016977) xxxxx
    • (017683) xxxxx
    • (017684) xxxxx
    • (017687) xxxxx
    • (019467) xxxxx
    • (011x) xxx xxxx
    • (01x1) xxx xxxx
    • (01xxx) xxxxxx
    • (02x) xxxx xxxx
    • 03xx xxx xxxx
    • 055 xxxx xxxx
    • 056 xxxx xxxx
    • 070 xxxx xxxx
    • 07624 xxxxxx
    • 076 xxxx xxxx
    • 07xxx xxxxxx
    • 08xx xxx xxxx
    • 09xx xxx xxxx

     

    Valid formats for geographic numbers include 2+8, 3+7, 4+6, 4+5, 5+5 and 5+4 (and 0+10 for NDO numbers).

    Non-geographic numbers mostly use 0+10 format, but some 0800 numbers and all 0500 numbers use 0+9 format.

    Most code found on the web caters for only a few of these, not the full set.

    The international format adds +44 and a space before the NSN digits.

    The national format adds the 0 trunk code before the NSN digits. For 01 and 02 numbers the area code should be in parentheses, except for NDO numbers (NDO numbers are those where subscriber number begins 0 or 1).

    NDO numbers are National Dialling Only. These have been around for several decades and need the area code to be dialled even when called locally from within the same area. These numbers are used for alarm systems, computer communication systems and other lines that are not dialled for voice calls. They are also used as the termination point for non-geographic numbers. NDO numbers are not supposed to be advertised nor directly called by subscribers. NDO numbers are always 0+10 format.

    There are proposals to open up the NDO ranges in some areas for use by normal subscribers starting some time in 2012.

     

     

    >> Dear Sir sir you can replace the sign "+" sign with "00" <<

    >> "+44 (0) 499 472001"  --------------->  "0044 (0) 499 472001" <<

    NO! With 00 on the beginning, the number is only usable in a subset of countries.

    The US and Canada uses 011 not 00 for international access.

    Other countries use other digits. There's a list at:  

     http://en.wikipedia.org/wiki/International_dialing_prefix

    • Edited by lesset Monday, October 17, 2011 7:37 AM
    Saturday, October 15, 2011 6:21 PM