Thousand separator to large number in SQL Server 2005

Question

We have a table where we are using a column to store the price of the item. The column size is 17.2 decimal. We need to have both thousand separator and decimal separator in the string. We are currently using this:

SELECT '$' + convert(varchar,cast('2123232322323.21' as money),-1) as Price

But it raises an error if the size exceed 15 digits.

try this SELECT '$' + convert(varchar,cast('212323232232123.21' as money),-1) as Price — Pankaj Saha, Jun 08 '16 at 16:56
show error "Arithmetic overflow error converting expression to data type money." — Pankaj Saha, Jun 08 '16 at 16:57
Why not leave the formatting to the front end? What happens if you need to show your price in Euros? Or if you want to sort in the front end? — Sean Lange, Jun 08 '16 at 16:57
If you mean more than 15 digits to left of the decimal point then that would be expected as the range for money is -922,337,203,685,477.5808 to 922,337,203,685,477.5807 — OldBoyCoder, Jun 08 '16 at 17:00
As an aside, please always declare the length of a varchar: (convert(varchar(20),....)` — Lamak, Jun 08 '16 at 17:01
Still not working. SELECT '$' + convert(varchar(25),cast('1121223232322323.21' as money),-1) as Price — Pankaj Saha, Jun 08 '16 at 17:02
That wasn't meant to fix your issue, that's why I said "as an aside". Anyway, it runs fine for me even in SQL Server 2000 — Lamak, Jun 08 '16 at 17:03
@OldBoyCoder its not showing in proper format.The result is $2123232322321243.21 — Pankaj Saha, Jun 08 '16 at 17:04
Yes, sorry I deleted my comment as I realized it wasn't doing what you wanted. — OldBoyCoder, Jun 08 '16 at 17:05
@JamesZ I think that op wants to add the thousands and decimal separator — Lamak, Jun 08 '16 at 17:07
Possible duplicate of [How do I format a number with commas in T-SQL?](http://stackoverflow.com/questions/4377352/how-do-i-format-a-number-with-commas-in-t-sql) — James Z, Jun 08 '16 at 17:12
You said that it raises an error if the size exceed 15 digits. It's normal because the limits of type MONEY is `-922,337,203,685,477.5808` to `922,337,203,685,477.5807` according to [money and smallmoney (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms179882.aspx) — Mincong Huang, Jun 08 '16 at 17:22
Yes. That's why. I need to convert the value of 17.4 to currency format. — Pankaj Saha, Jun 09 '16 at 03:39

score 1 · Answer 1 · answered Jun 09 '16 at 08:30

The money type does not have sufficient range for the numbers that the OP is using. ideally this sort of thing would be done in the presentation layer but the OP wants a SQL solution so I've had a go at a TSQL solution. Someone with better TSQL than me can improve this but I believe it'll do what the OP wants. Ideally it'd be made into a more generic User Defined function. It doesn't take into account locales (some European countries use '.' as the thousand separator and ',' as the decimal point).

DECLARE @Price decimal(19,2)
DECLARE @PriceString varchar(20)
DECLARE @DP int
DECLARE @Decimals varchar(3)
DECLARE @ResultString varchar(25)

SELECT @Price = 12345678901234567.89
SELECT @PriceString = CONVERT(varchar(20), @Price)
SELECT @DP = CHARINDEX('.', @PriceString)
SELECT @Decimals = SUBSTRING(@PriceString, @DP, LEN(@PriceString)-@DP+1)
SELECT @PriceString = SUBSTRING(@PriceString, 1, @DP-1)
SELECT @PriceString = REVERSE(@PriceString)
DECLARE @ix int
SELECT @ResultString = ''
SELECT @ix = 1
WHILE @ix <= LEN(@PriceString)
BEGIN
    SELECT @ResultString = SUBSTRING(@PriceString, @ix, 1) + @ResultString
    if @ix %3 = 0 AND @ix <> LEN(@PriceString) 
    BEGIN
        SELECT @ResultString = ',' + @ResultString

    END
    SELECT @ix = @ix +1
END
SELECT @ResultString = @ResultString + @Decimals
SELECT @ResultString

Thousand separator to large number in SQL Server 2005

1 Answers1