Insert Commas Into Number String

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Insert Commas Into Number String

Insert Commas Into Number String

Rate Topic

Display Mode

Topic Options

Author

Message

Jesse McLain

Posted Wednesday, April 01, 2009 3:06 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 06, 2013 8:47 AM
Points: 304, Visits: 552

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

unladenswallow

Posted Tuesday, April 28, 2009 1:02 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 3:50 PM
Points: 11, Visits: 33

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

jcrawf02

Posted Tuesday, April 28, 2009 6:05 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:36 PM
Points: 2,549, Visits: 18,880

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

T C

Posted Tuesday, April 28, 2009 7:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 22, 2013 9:01 PM
Points: 4, Visits: 515

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

RyanRandall

Posted Wednesday, April 29, 2009 3:21 AM

SSCommitted

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

jdarsley

Posted Friday, October 30, 2009 5:55 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2013 4:48 AM
Points: 49, Visits: 487

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

« Prev Topic | Next Topic »

Permissions