How to convert 15000000.00 to 15,000,000 to SQL server?

Question

There is one value of 15000000.00 with a numeric data type on the SQL server.

How can I get or convert a value of 1,500,000.00 to 15,000,000?

Answer 1

The database server is the wrong place for this. Localization should be processed in the presentation layer, and not in the data warehouse.

Answer 2

Since you are working with a type of money or want the result to be a type of money, it can be as simple as:

SELECT CONVERT(VARCHAR, @money_val, 1) -- you may have to cast to money from int first 

This link will open for a deeper discussion:

Here is an excerpt:

Comma formatting is available only for MONEY. If you want a comma INT, DECIMAL, etc., you must give it MONEY and do the convert with the flag of style 1.

DECLARE @m DECIMAL (10.2) SELECT @m = '23456789.25'

SELECT CONVERT (VARCHAR, @m, 1) AS DecimalValue, CONVERT (VARCHAR, CAST (@m AS MONEY), 1) AS MoneyValue / * DecimalValue
Moneyvalue

---

23456789.25 23 456 789.25 * /

Answer 3

This is not a conversion. 15,000,000.00 (German writing) is a string representation of 15 million. The decimal type does not save the string, but the value.

All the row representations that you see are basically visualization - in the enterprise manager or software in accordance with the settings (locale) when creating the row.

Answer 4

You do not want to save numerical data in formats. You can do this by selecting:

SELECT CONVERT(varchar(12), value, 1) AS formattedvalue FROM table

The last 1 is the conversion style that fits into. and, characters (depending on your local settings and number type).

If you ask me, I would not even let the SQL database server worry about this and format the number in the code itself.

Answer 5

I have always successfully used

 SELECT Convert(VARCHAR, Cast(100000.01 AS MONEY), 1) Amount 

Results:
Amount
-----------------------------
100,000.01