Formatting a numeric value

Dev Shed Forums Sponsor:

April 30th, 2012, 11:05 AM

macrado

Contributing User

Join Date: Oct 2003

Posts: 75

Time spent in forums: 21 h 17 m 4 sec
Reputation Power: 10

Formatting a numeric value

OK, so let me preface this by saying that I know that formatting a numeric value should normally be done in the presentation layer, however, I have no say in the matter.

My problem is that I have a numeric value that needs to be returned pre-formatted with commas and three decimal places. Originally, the query returns the value as:

sum(convert(decimal(18,3), col6val)

This works, but there are no commas.

Since there is no simple way to format the sting, I had to investigate a solution. I found a few solutions saying to cast it as money, and convert that value to varchar, as follows:

convert(varchar, cast(sum(convert(decimal(18,3), col6val)) as money),1)

So as I read it, this converts col6val to decimal value with 3 decimal places of precision. Then that is summed. The resulting value is cast as money to get the commas. Finally, this is converted to varchar to preserve those commas in the return value.

When I execute this, though, I get the following error:

Error converting data type varchar to numeric.

I should say that this value is being selected into a temporary table, and that this field is of type varchar(50). If I remove the convert(varchar...) part of the query, I get no errors, but the string is still not formatted.

Can anyone help me here? Is there something I'm doing wrong, or is there a better way to format a numeric value with commas?

Thanks for any help!

__________________
~~Macrado~~

April 30th, 2012, 02:51 PM

macrado

Contributing User

Join Date: Oct 2003

Posts: 75

Time spent in forums: 21 h 17 m 4 sec
Reputation Power: 10

I found a workaround, figured I'd update here.

First of all, there was/is something funky going on with the temporary table that I'm selecting this data into. The field I'm referencing is a varchar field, but if I select any non-numeric characters into it, I get the error I mentioned earlier. I don't know what's going on there though, it's kind of weird. There are multiple select queries that populate that table, and in at least one of those queries, I select string data into it and it works fine. So who knows what's up there.

Anyway, my solution was to select it into the temp table as numeric data, and then just run an update query to add the commas after the fact.

I found a handy function that formats a string with commas at every 3 characters at http://stackoverflow.com/questions/4377352/how-do-i-format-a-number-with-commas-in-t-sql

Code:

CREATE FUNCTION [dbo].[fn_FormatWithCommas] 
(
    -- Add the parameters for the function here
    @value varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @WholeNumber varchar(50) = NULL, @Decimal varchar(10) = '', @CharIndex int = charindex('.', @value)

    IF (@CharIndex > 0)
        SELECT @WholeNumber = SUBSTRING(@value, 1, @CharIndex-1), @Decimal = SUBSTRING(@value, @CharIndex, LEN(@value))
    ELSE
        SET @WholeNumber = @value

    IF(LEN(@WholeNumber) > 3)
        SET @WholeNumber = dbo.fn_FormatWithCommas(SUBSTRING(@WholeNumber, 1, LEN(@WholeNumber)-3)) + ',' + RIGHT(@WholeNumber, 3)



    -- Return the result of the function
    RETURN @WholeNumber + @Decimal

Formatting a numeric value

Developer Shed Advertisers and Affiliates