Thankfully(?), in SQL Server 2012+, you can now use FORMAT() to achieve this:
FORMAT(@s,'#,0.0000')
In prior versions, at the risk of looking real ugly
[Query]:
declare @s decimal(18,10);
set @s = 1234.1234567;
select replace(convert(varchar,cast(floor(@s) as money),1),'.00',
'.'+right(cast(@s * 10000 +10000.5 as int),4))
In the first part, we use MONEY->VARCHAR to produce the commas, but FLOOR() is used to ensure the decimals go to .00. This is easily identifiable and replaced with the 4 digits after the decimal place using a mixture of shifting (*10000) and CAST as INT (truncation) to derive the digits.
[Results]:
| COLUMN_0 |
--------------
| 1,234.1235 |
But unless you have to deliver business reports using SQL Server Management Studio or SQLCMD, this is NEVER the correct solution, even if it can be done. Any front-end or reporting environment has proper functions to handle display formatting.
Answer from RichardTheKiwi on Stack OverflowThankfully(?), in SQL Server 2012+, you can now use FORMAT() to achieve this:
FORMAT(@s,'#,0.0000')
In prior versions, at the risk of looking real ugly
[Query]:
declare @s decimal(18,10);
set @s = 1234.1234567;
select replace(convert(varchar,cast(floor(@s) as money),1),'.00',
'.'+right(cast(@s * 10000 +10000.5 as int),4))
In the first part, we use MONEY->VARCHAR to produce the commas, but FLOOR() is used to ensure the decimals go to .00. This is easily identifiable and replaced with the 4 digits after the decimal place using a mixture of shifting (*10000) and CAST as INT (truncation) to derive the digits.
[Results]:
| COLUMN_0 |
--------------
| 1,234.1235 |
But unless you have to deliver business reports using SQL Server Management Studio or SQLCMD, this is NEVER the correct solution, even if it can be done. Any front-end or reporting environment has proper functions to handle display formatting.
without considering this to be a good idea...
select dbo.F_AddThousandSeparators(convert(varchar, convert(decimal(18, 4), 1234.1234567), 1))
Function
-- Author: bummi
-- Create date: 20121106
CREATE FUNCTION F_AddThousandSeparators(@NumStr varchar(50))
RETURNS Varchar(50)
AS
BEGIN
declare @OutStr varchar(50)
declare @i int
declare @run int
Select @i=CHARINDEX('.',@NumStr)
if @i=0
begin
set @i=LEN(@NumStr)
Set @Outstr=''
end
else
begin
Set @Outstr=SUBSTRING(@NUmStr,@i,50)
Set @i=@i -1
end
Set @run=0
While @i>0
begin
if @Run=3
begin
Set @Outstr=','+@Outstr
Set @run=0
end
Set @Outstr=SUBSTRING(@NumStr,@i,1) +@Outstr
Set @i=@i-1
Set @run=@run + 1
end
RETURN @OutStr
END
GO
sql server - How do I format a number with commas in T-SQL? - Stack Overflow
sql server - Best way to put commas into large numbers - Database Administrators Stack Exchange
Select convert to 2 decimal places, comma, right aligned – SQLServerCentral Forums
Format a number with commas but without decimals in SQL Server 2008 R2? - Stack Overflow
Videos
In SQL Server 2012 and higher, this will format a number with commas:
select format([Number], 'N0')
You can also change 0 to the number of decimal places you want.
While I agree with everyone, including the OP, who says that formatting should be done in the presentation layer, this formatting can be accomplished in T-SQL by casting to money and then converting to varchar. This does include trailing decimals, though, that could be looped off with SUBSTRING.
SELECT CONVERT(varchar, CAST(987654321 AS money), 1)
If you're on SQL Server 2012+, and you want to do it (without decimal places):
SELECT x = FORMAT(2036150, N'N0')
If you're on an earlier version, you have to jump through some hoops:
SELECT x = REPLACE(CONVERT(nvarchar(30), CONVERT(money, 2036150), 1), N'.00', N'');
TSQL's FORMAT function can do that. EG
select format(1232131312342132123.2222,'0,###.##')
outputs
1,232,131,312,342,132,123.22
If you want to use a different separator, you still use , which has a special meaning in .Net format string, and then replace the , with something else. EG:
select replace(format(123445678.2222,'#,###.##'),',','_')
outputs
123_445_678.22
DECLARE @val varchar(50)
set @val = CONVERT(varchar(50), CAST(1112 AS money), 1)
SELECT left(@val, len(@val) - 3)
This also works with digits after the decimal point:
DECLARE @val varchar(50)
set @val = CONVERT(varchar(50), CAST(1112.56 AS money), 1)
SELECT left(@val, len(@val) - 3)
Note: as @Mahmoud Gamal points out, formatting is often more suited to be performed in the front-end.
Like so:
SELECT REPLACE(CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1), '.00', '');
This will always work fine. Since CONVERT(VARCHAR(20), CAST(1112 AS MONEY), 1) will always return a number with .00. However, the MitchWheat's answer is better in case there is a number with decimal numbers after the comma.
Note that: You should consider to do this formatting stuff in the front end application. T-SQL is not about formatting.
(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.)
You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then convert the comma into a period / decimal and you are done:
SELECT CONVERT(NUMERIC(10, 2),
REPLACE(
REPLACE('7.000,45', '.', ''),
',', '.'
)
) AS [Converted];
Returns:
7000.45
For the sake of completeness, I should mention that I also tried:
SET LANGUAGE Greek;Looking at various format styles for CONVERT, but nothing applies here.
The FORMAT function, but the input type must be a numeric or date/time/datetime value (that and it was introduced in SQL Server 2012, so not applicable to SQL Server 2008 R2 or older).
And nothing else seemed to work. I was hoping to find something more elegant than two REPLACE calls, but so far no such luck.
Also, just to mention, while not a pure T-SQL solution, this can also be accomplished via SQLCLR. And, there is a pre-done function that does this in the SQL# library (that I wrote) named String_TryParseToDecimal. This function is available in the Free version, and works in every version of SQL Server starting with SQL Server 2005:
SELECT SQL#.String_TryParseToDecimal('7.000,45', 'el-GR');
Returns:
7000.45000000000000000000
What version of SQL Server are you using? From SQL Server 2012 onwards you can use TRY_PARSE with its USING culture argument. You can also use PARSE, the difference being PARSE will fail if the conversion fails and TRY_PARSE will return a NULL, eg
DECLARE @t TABLE ( x VARCHAR(10) )
INSERT INTO @t
VALUES ( '7.000,45' ), ( 'xxx' )
SELECT x,
TRY_PARSE( x AS NUMERIC(10,2) USING 'El-GR' ) x
FROM @t
HTH