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

Question

There is a one value 15000000.00 with Numeric datatype in SQL server.

How can i get or convert the value 15000000.00 to 15,000,000?

Answer 1

This is not a conversion. 15.000.000,00 (german writing) is the string representation of 15 million. A decimal data type does not store the string, but the value.

All string represenration you see are basically visualizations - in enterprise manager or software according to the settings (locale) when generating the string.

Answer 2

The discussion here may give you an answer.

Answer 3

The database server is the wrong place to do this. Localization should be handled in the presentation layer, not the datastore.

Answer 4

Since you're working with the money type, or you want the result to be a money type it could be as simple as:

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

Check out this link for a deeper discussion:

Here is an excerpt:

Comma-formatting is available only for MONEY data types. If you want to comma-format INT, DECIMAL etc, you should cast it to MONEY and do a convert with style flag 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 5

You don't want to save your numeric data in formats. You can do this when selecting:

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

The last 1 is a convert style that puts in the . and , signs (depending on your local settings and numeric type).

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

Answer 6

I've always used the following with success

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

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