CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005type-conversioncurrency
Tim Schmelter
Tim Schmelter

Reputation: 460138

Show comma instead of point as decimal separator

I just want to get the right number format here in germany, so i need to show commas as decimal separator instead of points. But this...

DECLARE @euros money
SET @euros = 1025040.2365
SELECT CONVERT(varchar(30), @euros, 1)

Displays 1,025,040.24 instead of 1.025.040,24 (or 1025040,24). In C# it would be simple to provide the appropriate CultureInfo but how to do it in T-SQL?

Do i really need to use REPLACE? But even if, how to replace 1,025,040.24 correctly?

Upvotes: 19

Views: 141148

Answers (5)

Phate01
Phate01

Reputation: 1795

To provide the appropriate culture info, in SQL 2012 there is the FORMAT() function. Here's an example:

declare @f float = 123456.789;

select
  [raw]      = str(@f,20,3)
 ,[standard] = cast(format(@f, 'N', 'en-US') as varchar(20))
 ,[German] = cast(format(@f, 'N', 'de-DE') as varchar(20))

returns

raw                  |standard   |German     |
---------------------|-----------|-----------|
          123456.789 |123,456.79 |123.456,79 |

You can also specify in the second parameter a custom format string with the same rules as for .NET.

Docs: https://msdn.microsoft.com/en-US/library/hh213505.aspx

Upvotes: 42

the Explorer
the Explorer

Reputation: 103

You can first replace thousand separator comma(,) to a Zero length string (''), and then you can replace Decimal('.') to comma(',') in the same select statement.

Upvotes: 0

FutbolFan
FutbolFan

Reputation: 13723

You could use replace something like this:

DECLARE @euros money 
SET @euros = 1025040.2365

SELECT REPLACE(REPLACE(CONVERT(varchar(30), @euros, 1),',',''),'.',',');

SQL Fiddle Demo

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

DECLARE @euros money
SET @euros = 1025040.2365
SELECT REPLACE(CONVERT(varchar(30), @euros, 0), '.', ',')

should do it (at least to get 1025040,24)

Upvotes: 6

Andrey Korneyev
Andrey Korneyev

Reputation: 26856

Well, as far as I know, there are no culture-specific options for convert available.

So you can do it using replaces (yes, it looks a bit ugly...)

select 
    replace(replace(replace(convert(varchar(30), @euros, 1), ',', '|'), '.', ','), '|', '.')

Idea: first change comma to something, then change dot to comma, and then "something" back to dot.

Upvotes: 19

Related Questions