Return Numeric Data with a Thousands Separator

Posted: October 10, 2011 in SQL Server 2005

To return a thousands separator for any numeric data, you can try this following:

-- Declare variables
DECLARE @ln_value INT;
DECLARE @lm_value MONEY;
DECLARE @lv_value VARCHAR(20);

-- Initialise variables
SET @ln_value = 123456;

-- Convert to money
SET @lm_value = CAST(@ln_value AS MONEY);

-- Return value
SET @lv_value = CAST(CONVERT(VARCHAR(20), @lm_value, 1) AS VARCHAR(20));

-- Strip out decimals
SELECT SUBSTRING(@lv_value, 1, CHARINDEX('.', @lv_value ) - 1);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s