SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Jesse McLain / Insert Commas Into Number String / Latest Posts

RE: Insert Commas Into Number String

jdarsley — Fri, 30 Oct 2009 05:55:46 GMT

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.CheersJohn

RE: Insert Commas Into Number String

RyanRandall — Wed, 29 Apr 2009 03:21:03 GMT

[quote][b]unladenswallow (4/28/2009)[/b][hr]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.[/quote]I couldn't resist doing it. Can this be improved?[code]--inputsdeclare @s varchar(20)--set @s = '12345.6789'--set @s = '1234567'set @s = '123456789.01234567'--/--calculationSELECT REPLACE(CONVERT(varchar(24), FLOOR(CONVERT(money, @s)),1), '.00', substring(@s, charindex('.', @s + '.'), 20))--//*outputs12,345.67891,234,567123,456,789.01234567*/[/code]

RE: Insert Commas Into Number String

T C — Tue, 28 Apr 2009 07:47:53 GMT

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)

RE: Insert Commas Into Number String

jcrawf02 — Tue, 28 Apr 2009 06:05:43 GMT

Nice trick, swallow!African or European?

RE: Insert Commas Into Number String

unladenswallow — Tue, 28 Apr 2009 01:02:51 GMT

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.00If 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.

Insert Commas Into Number String

Jesse McLain — Wed, 01 Apr 2009 03:06:41 GMT

Comments posted to this topic are about the item [B]Insert Commas Into Number String[/B]