1 2 Previous Next 16 Replies Latest reply: Jul 16, 2012 9:08 AM by Frank Kulash RSS

    TO_char error - Number format Mask - need phone number format

    793663
      Hi all,

      i got a stuck in a basic thing. my input will be number (say 1 to 9999999999) i want that to be populated in this format ( 999-999-9999). i tried the below query, it doesnt help me.

      select to_char(1234567890,'999-999-9999') from dual;

      please tell me which function is most appropriate for this.


      my input may be a single digit also, but my output should be in ten digits (ie: if i gave 1, o/p should be 000-000-0001)

      kindly help me.

      thanks,
      Jeevanand.K
        • 1. Re: TO_char error - Number format Mask - need phone number format
          sybrand_b
          1 No four digit version
          2 Question boiling down to 'My car is stuck please fix my car', as there is no sign of error or incorrect output.
          3 You didn't read the to_char documentation.
          If you want leading zeroes read documentation and replace the '9' by a '0'

          Sigh. Why is everyone here too lazy to

          - read documentation
          - write a proper question


          You are getting paid for this 'work' , I don't!!

          ------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: TO_char error - Number format Mask - need phone number format
            theoa
            Try something like this:
            sql> select to_char(1234567890,'000G000G0000', 'nls_numeric_characters=''.-''') from dual;
            
            TO_CHAR(12345
            -------------
             123-456-7890
            • 3. Re: TO_char error - Number format Mask - need phone number format
              BluShadow
              Or something like this...
              SQL> ed
              Wrote file afiedt.buf
              
                1* select regexp_replace(to_char(1234567890),'([0-9]{3})([0-9]{3})([0-9]{4})','\1-\2-\3') from dual
              SQL> /
              
              REGEXP_REPLA
              ------------
              123-456-7890
              • 4. Re: TO_char error - Number format Mask - need phone number format
                Frank Kulash
                Hi,

                I don't think TO_CHAR alone can do that.
                You can use TO_CHAR in combination with string manipulation fucntions, like this:
                SELECT     REGEXP_REPLACE ( TO_CHAR (phone_num, 'FM0000000000')
                                 , '(...)(...)(....)'
                                 , '\1-\2-\3'
                                 )     AS phone_txt
                FROM     table_x
                ;
                SUBSTR will be sa little faster.
                • 5. Re: TO_char error - Number format Mask - need phone number format
                  Keith Jamieson
                  you could just use the following:
                  Its not as elegant, but it will work
                  select substr(lpad('1234567890',10,'0'),1,3)||'-'|| 
                         substr(lpad('1234567890',10,'0'),4,3)||'-'|| 
                         substr(lpad('1234567890',10,'0'),7,4) 
                  from dual;
                  select substr(lpad('9',10,'0'),1,3)||'-'|| 
                         substr(lpad('9',10,'0'),4,3)||'-'|| 
                         substr(lpad('9',10,'0'),7,4) 
                  from dual;
                  • 6. Re: TO_char error - Number format Mask - need phone number format
                    EdStevens
                    Jeevanand K wrote:
                    Hi all,

                    i got a stuck in a basic thing. my input will be number (say 1 to 9999999999) i want that to be populated in this format ( 999-999-9999). i tried the below query, it doesnt help me.

                    select to_char(1234567890,'999-999-9999') from dual;

                    please tell me which function is most appropriate for this.


                    my input may be a single digit also, but my output should be in ten digits (ie: if i gave 1, o/p should be 000-000-0001)

                    kindly help me.

                    thanks,
                    Jeevanand.K
                    And once again we see the problem with using improper data types. In spite of the term (tele)phone number, it is NOT a number. It is a string of characters which, by recent (within my lifetime) convention we agree to use only numeric characters. Well, not quite only numeric, but also the special character "-" and in some localities a ".". And the particular and specific difficulty with keeping phone "numbers" (as well as social security "numbers" in the US; as well as US Postal zip codes) as NUMBER data type is the loss of leading zeros. These particular data elements are, by nature, character strings, not numbers. But they are character strings with some particular validation requirements that have to be built in to the application at some point, or perhaps with a ON INSERT/ON UPDATE trigger to call a validation function.

                    What?!?! You think those letters on your telephone keypad were put there for texting? Wrong. They were there LOONNGG before cell phones and text messages were even a fanciful dream. They were there when telephones had "dials" instead of keypads. When I was a child, my telephone "number" was WH9-3187. The fact that the phone company changed the convention to publish the number as 949-3187 changed neither the actual 'dialing' sequence nor the nature of the data.
                    • 7. Re: TO_char error - Number format Mask - need phone number format
                      John Spencer
                      EdStevens wrote:
                      What?!?! You think those letters on your telephone keypad were put there for texting? Wrong. They were there LOONNGG before cell phones and text messages were even a fanciful dream. They were there when telephones had "dials" instead of keypads. When I was a child, my telephone "number" was WH9-3187. The fact that the phone company changed the convention to publish the number as 949-3187 changed neither the actual 'dialing' sequence nor the nature of the data.
                      Ed:

                      You phone number wouldn't have been WH9, at least where I grew up, the WH was meaningful. My phone number was PL9-1744, but if anyone asked me my number it was Plymouth 9-1744 :-)

                      Amazing how long you remember those things.

                      John
                      • 8. Re: TO_char error - Number format Mask - need phone number format
                        Nicosa-Oracle
                        Frank,

                        theoa's solution does it like a charm :
                        [11.2] Pri @ Bepripd1 > l
                          1  with t(n) as (
                          2  select 1234567890 from dual
                          3  union all select 0 from dual
                          4  )
                          5* select n, to_char(n,'fm000G000G0000','nls_numeric_characters=''.-''') c from t
                        [11.2] Pri @ Bepripd1 > /
                        
                                 N C
                        ---------- ------------
                        1234567890 123-456-7890
                                 0 000-000-0000
                        • 9. Re: TO_char error - Number format Mask - need phone number format
                          riedelme
                          Jeevanand K wrote:
                          Hi all,

                          i got a stuck in a basic thing. my input will be number (say 1 to 9999999999) i want that to be populated in this format ( 999-999-9999). i tried the below query, it doesnt help me.

                          select to_char(1234567890,'999-999-9999') from dual;

                          please tell me which function is most appropriate for this.


                          my input may be a single digit also, but my output should be in ten digits (ie: if i gave 1, o/p should be 000-000-0001)

                          kindly help me.

                          thanks,
                          Jeevanand.K
                          Yet another answer. I would have tried the original attempt first; I think it didn't work because the dashes were interpreted as sign indicators. Working with Oracle dates is such fun!
                           select substr(to_char(9999999999),1,3)||'-'||
                                  substr(to_char(9999999999),4,3)||'-'||
                                  substr(to_char(9999999999),7,4)
                             from dual
                          
                          SUBSTR(TO_CH
                          ------------
                          999-999-9999
                          I remember the alpha phone prefixes. Stan Freberg (a satarist) had a song about how doing away with them was an attempt by the evil phone company to control us in the mid '60s. An interesting exercise would be to convert the middle 3 digits to their alpha counterparts - simple enough with DECODE or CASE I suppose but few enough of us remember the "MA7" type prefixes for this to be practical. I'm still unhappy about having to do the area code for every call :(
                          • 10. Re: TO_char error - Number format Mask - need phone number format
                            793663
                            Ed,

                            one hundred percent useful information. thanks a lot. Your experience speaking here, i respect that.


                            Jeevanand.K
                            • 11. Re: TO_char error - Number format Mask - need phone number format
                              Marwim
                              I remember the alpha phone prefixes.
                              I don't know whether/when there had been numbers like this in Germany. But there is "Chattanooga Choo Choo", I remember that we played it with our band and there is a part where you have to shout "Pennsylvania 6-5-O-O-O" :-)

                              Marcus
                              • 12. Re: TO_char error - Number format Mask - need phone number format
                                Marwim
                                In spite of the term (tele)phone number, it is NOT a number.
                                Fixed formats for telephone numbers are always a problem.
                                Think about vanity phone numbers, variants like emergency calls (911) or international numbers

                                Regards
                                Marcus
                                • 13. Re: TO_char error - Number format Mask - need phone number format
                                  EdStevens
                                  John Spencer wrote:
                                  EdStevens wrote:
                                  What?!?! You think those letters on your telephone keypad were put there for texting? Wrong. They were there LOONNGG before cell phones and text messages were even a fanciful dream. They were there when telephones had "dials" instead of keypads. When I was a child, my telephone "number" was WH9-3187. The fact that the phone company changed the convention to publish the number as 949-3187 changed neither the actual 'dialing' sequence nor the nature of the data.
                                  Ed:

                                  You phone number wouldn't have been WH9, at least where I grew up, the WH was meaningful. My phone number was PL9-1744, but if anyone asked me my number it was Plymouth 9-1744 :-)

                                  Amazing how long you remember those things.

                                  John
                                  Well, it was known as Whitney 9-3187. Later changed (why, I don't know) to SUnset 9-3187. As you well know, the exchanges had names, and generally the first two letters of the name were the leading characters of the telephone "number". Several years ago I was confronted with a dial telephone and was amazed (not only that any were still in service) at how awkward it felt to dial a number when it used to be the most natural thing in the world.

                                  and to stay on subject:
                                  NOTE TO DEVELOPERS: Telephone numbers are not numbers. They are character strings of a known, fixed length and limited to a verifiable subset of available characters, those characters being 0-9. Same for US Social Security "Numbers". Same for US Postal zip codes.
                                  • 14. Re: TO_char error - Number format Mask - need phone number format
                                    793663
                                    Nicosa,

                                    can you tell me why you used the UNION ALL there. i am not clear with that. what is the necessity to bring the WITH clause here. as mentioned by the theoa we can achive it by means to_char alone,right ?. based on the theoa solution i framed like this,

                                    INSERT INTO phonenumber VALUES(TO_CHAR(phoneseq.nextval,'FM000G000G0000', 'nls_numeric_characters=''.-'''));


                                    is there any problem in the upper query ?



                                    Thanks,
                                    Jeevanand.K
                                    1 2 Previous Next