Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert Commas Into Number String Expand / Collapse
Author
Message
Posted Wednesday, April 1, 2009 3:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, Visits: 567
Comments posted to this topic are about the item Insert Commas Into Number String

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #687713
Posted Tuesday, April 28, 2009 1:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:12 PM
Points: 13, Visits: 65
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.



Post #705542
Posted Tuesday, April 28, 2009 6:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:44 AM
Points: 1,332, Visits: 19,320
Nice trick, swallow!

African or European?


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

"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."
Post #705712
Posted Tuesday, April 28, 2009 7:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 11:55 AM
Points: 4, Visits: 527
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)

Post #705826
Posted Wednesday, April 29, 2009 3:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 29, 2012 11:22 AM
Points: 1,755, Visits: 4,652
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.
Post #706556
Posted Friday, October 30, 2009 5:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 9:35 AM
Points: 67, Visits: 543
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



Post #811486
Posted Saturday, June 8, 2013 10:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 8, 2013 10:23 AM
Points: 1, Visits: 6
Yes, a waste of time. You can do that with just one sentence.
Post #1461293
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse