How to change the number 12345678 to 12,345,678?

Thank YOU.

I couldn't see a proper way of masking the data.

Have a play with something like this...



CREATE FUNCTION fn_ADD_THOUSAND_SEPARATOR (@input_value NUMERIC) RETURNS VARCHAR(75) AS

BEGIN

    DECLARE @input VARCHAR(50)
    DECLARE @output VARCHAR(50)

    SELECT @input = convert(varchar, @input_value)
    SELECT @output = ''

    WHILE LEN(@input) > 2
    BEGIN
        SELECT @output = ',' + RIGHT(@input, 3) + @output
        SELECT @input = LEFT(@input, LEN(@input) - 3)
    END

    IF (LEN(@input) > 0)
    BEGIN
        SELECT @output = @input + @output
    END
    ELSE
    BEGIN
        SELECT @output = RIGHT(@output, LEN(@output) - 1)
    END

    RETURN @output

END

GO

SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(12)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(123)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1234)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(12345)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(123456)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1234567)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(12345678)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(123456789)
SELECT dbo.fn_ADD_THOUSAND_SEPARATOR(1234567890)




Regards,

Sean Anderson

First of all: This is not something that should be done at databaselevel - formatting belongs in the presentation-layer.

That being said - Sean's solution seems fine, except it won't handle floating point values.