none
number formatted sa XXXX-XX-XXXX RRS feed

  • Question

  • hi all i want to format the number into this format XXXX-XX-XXXX . . what is the correct command line that should i use...please help me..
    Monday, May 28, 2012 4:11 AM

Answers

  • Just replace the variable @num with the column and add the from clause:

    SELECT LEFT(IDNumber, 6) + '-'
           + SUBSTRING(IDNumber, 7, 2) + '-'
           + RIGHT(IDNumber, 4) AS FormatedNum
    FROM yourTable


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by wanshazict Monday, May 28, 2012 1:49 PM
    Monday, May 28, 2012 10:26 AM

All replies

  • Hello,

    What data type is your number? Do it have always the same length (10 digits)?


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Monday, May 28, 2012 7:05 AM
  • Can you check-out below thread

    http://social.msdn.microsoft.com/Forums/en-CA/transactsql/thread/c7259b90-143f-424b-9a7f-cf76d086c7b2


    Regards,
    Ahmed Ibrahim
    SQL Server Setup Team
    This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
    This can be beneficial to other community members reading the thread.

    Monday, May 28, 2012 7:09 AM
    Moderator
  • i am using varchar as data type...corecction here,actually it is XXXXXX-XX-XXXX @ 12 digits and always have the same length...
    Monday, May 28, 2012 9:27 AM
  • You you can solve it this way:

    DECLARE @num varchar(12);
    SET @num = '123456789012'
    
    SELECT LEFT(@num, 6) + '-'
           + SUBSTRING(@num, 7, 2) + '-'
           + RIGHT(@num, 4)


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Monday, May 28, 2012 9:32 AM
  • i think i dont get it yet...actually iam new@beginner in this...ok i tell you the situation and hope you can help me...

    example = i have the database named "test1" and a table named "table1" . . . . in the table i have attribute named as "IDNumber" . . so i want to formatted the "IDNumber" as XXXXXX-XX-XXXX . . can you give me full command how to write it....please..

    Monday, May 28, 2012 10:18 AM
  • Just replace the variable @num with the column and add the from clause:

    SELECT LEFT(IDNumber, 6) + '-'
           + SUBSTRING(IDNumber, 7, 2) + '-'
           + RIGHT(IDNumber, 4) AS FormatedNum
    FROM yourTable


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by wanshazict Monday, May 28, 2012 1:49 PM
    Monday, May 28, 2012 10:26 AM
  • its works...thanks a lot sir... :)
    Monday, May 28, 2012 1:48 PM