Post reply

Manipulating Decimal Format 0000000090.00000

  • December 8, 2009 at 10:13 pm

    #207043

    Hi,

    I'm bit new to MSSQL. I needed to convert a value from real datatype to nchar(16) datatype with format 9999999999.99999

    for example i have :

    350, the result should be 0000000350.00000.

    90, the result should be 0000000090.00000.

    14.60, the result should be 0000000014.60000.

    I cant find any help on any sites. Please help.

    Thanks in advance.

  • December 8, 2009 at 11:46 pm

    #1089662

    Hi,

    Is really need to show in this format?

    Ok try this

    declare @result numeric(16,5), @final nchar(16)

    set @result = 100

    set @final = REPLICATE('0',(10-len(floor(@result))))+ cast(@result as nchar(16))

    select @final

  • December 9, 2009 at 3:09 am

    #1089708

    Wow!

    🙂 This is a cool place to find help. Yes,I've been searching for this in the world and this is perfect!

    Thanks

  • December 9, 2009 at 3:14 am

    #1089712

    :w00t:

    you are welcome!!!

  • December 10, 2009 at 12:39 am

    #1090384

    You can actually do it a tiny bit simpler...

    SELECT REPLACE(STR(350,16,5),' ','0')

    STR returns NVARCHAR values.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 10, 2009 at 1:09 am

    #1090390

    Hi jeff,

    very nice

  • December 10, 2009 at 7:03 am

    #1090513

    Thanks, Arun... the only thing I haven't done is test it for performance... that's because I don't normally do such formatting in SQL Server. The only time I do something like that is for file exports with no GUI and I haven't had to do that in years.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 10, 2009 at 7:47 pm

    #1090960

    Ah, dang it. Sometimes I hate BOL... the STR function that I looked at to get the return type was for SQL Server Mobile. Why on Earth the Mobile and Enterprise editions would have different return types for the same function is totally beyond me. In the Enterprise and Standard editions, STR returns a CHAR datatype where the Mobile edition returns an NCHAR datatype. The implicit conversion from CHAR to NVCHAR for this application is certainly easy and fast, but I did have to post this correction about the datatype. Sorry for the confusion.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply