April 1, 2009 at 3:06 am
Comments posted to this topic are about the item Insert Commas Into Number String
April 28, 2009 at 1:02 am
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.
April 28, 2009 at 6:05 am
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."
April 28, 2009 at 7:47 am
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)
April 29, 2009 at 3:21 am
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-2 varchar(20)
--set @s-2 = '12345.6789'
--set @s-2 = '1234567'
set @s-2 = '123456789.01234567'
--/
--calculation
SELECT REPLACE(CONVERT(varchar(24), FLOOR(CONVERT(money, @s-2)),1), '.00', substring(@s, charindex('.', @s-2 + '.'), 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.
October 30, 2009 at 5:55 am
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-2))) * sign(convert(money, @s-2)), 1), '.00', substring(@s, charindex('.', @s-2 + '.'), 20))
There may be a more elegant fix than this.
Cheers
John
June 8, 2013 at 10:25 am
Yes, a waste of time. You can do that with just one sentence.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply