Insert Commas Into Number String

  • Comments posted to this topic are about the item Insert Commas Into Number String

  • If two decimal places are ok, you can just convert the string to money, then back to a string with a style of 1.

    SELECT CONVERT(varchar(24),CONVERT(money,'12345678'),1)

    returns: 12,345,678.00

    If it's important to leave the decimal part alone, it's easy to just do this to the characters to the left of the decimal point in the original string, and then replace the '.00' with the original decimal value, if any.

  • Nice trick, swallow!

    African or European?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • From what I remember, this code should also insert the appropriate commas, but then also get the number up to the decimal (after rounding).

    select Substring (CONVERT(varchar(25),round(cast('12345678.99' as money),0),1) ,1,patindex( '%.%',CONVERT(varchar(25),round(cast('12345678.99' as money),0),1)) -1)

  • unladenswallow (4/28/2009)


    If it's important to leave the decimal part alone, it's easy to just do this to the characters to the left of the decimal point in the original string, and then replace the '.00' with the original decimal value, if any.

    I couldn't resist doing it. Can this be improved?

    --inputs

    declare @S varchar(20)

    --set @S = '12345.6789'

    --set @S = '1234567'

    set @S = '123456789.01234567'

    --/

    --calculation

    SELECT REPLACE(CONVERT(varchar(24), FLOOR(CONVERT(money, @S)),1), '.00', substring(@s, charindex('.', @S + '.'), 20))

    --/

    /*outputs

    12,345.6789

    1,234,567

    123,456,789.01234567

    */

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Hi Ryan,

    One observation - it doesn't handle negative values.

    This fixes that problem, but at the expense of some readability:

    SELECT REPLACE(CONVERT(varchar(24), floor(abs(CONVERT(money, @S))) * sign(convert(money, @S)), 1), '.00', substring(@s, charindex('.', @S + '.'), 20))

    There may be a more elegant fix than this.

    Cheers

    John

  • Yes, a waste of time. You can do that with just one sentence.

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

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