SELECT FORMAT(12345,'#,0.00');
SELECT FORMAT(TotalArea,'#,0.00') from table;
Reference: https://msdn.microsoft.com/en-us/library/ee634206(v=sql.105).aspx
Answer from dasiimwe on Stack OverflowSELECT FORMAT(12345,'#,0.00');
SELECT FORMAT(TotalArea,'#,0.00') from table;
Reference: https://msdn.microsoft.com/en-us/library/ee634206(v=sql.105).aspx
Try this way:
SELECT REPLACE(CONVERT(VARCHAR, CONVERT(MONEY, TotalArea), 1), '.00', '')
FROM table
or
SELECT CAST(CONVERT(VARCHAR, CAST(123456 AS MONEY), 1) AS VARCHAR)
FROM table
SQL Thousand separator with round – SQLServerCentral Forums
sql server - Convert string numeric values with comma as decimal separator to NUMERIC(10, 2) - Database Administrators Stack Exchange
sql - Format number with space as thousand separator - Stack Overflow
how to add commas as thousand separator for numbers stored as text – SQLServerCentral Forums
Hi @Joyzhao-MSFT
I changed the language settings of the text box to de-DE and it helps without having to do any changes in the expression.
Hi @Nimisha Vernekar ,
As you are asking for dot(.) as 1000 separator, I am assuming that you do not have decimal points in number.
Now, if your filed is SalesAmount
=Replace(Format(Fields!SalesAmount.Value, "#,###0"),",",".")
And if you have decimals ( but now you will not able to distinguish separator and decimal point)
=Replace(Format(Fields!SalesAmount.Value, "#,###0.00"),",",".")
Only issue with the approach would be, when you export to excel not this column will treated as text.
Best Regards,
Joy
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
(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
For SQL Server 2008, I think your best bet for a "nicer" solution within SQL Server would be to write a custom CLR function to handle the formatting based on region/locale.
For SQL Server 2012 or later, the FORMAT function has been introduced.
I think this should work for you:
SELECT REPLACE(REPLACE(CONVERT(varchar, CONVERT(money, 12765763363.249999), 1), ',', ' '), '.', ',');
It will output the following:
12 765 763 363,25