CODEWITHSUNDEEP
sqlsql-servert-sql
user2675045
user2675045

Reputation: 193

SQL replace dot with comma

I have following code:

SELECT cast(Listenpreis*1.19 as decimal(29,2)) as Listenpreis
 FROM [SL_M03KNE].[dbo].[ARKALK]

I get this value: 5.59

I try to replace the dot to a komma so i get the Value: 5,59

I try the code:

SELECT replace((cast(Listenpreis*1.19 as decimal(29,2)) as Listenpreis),'.','))
 FROM [SL_M03KNE].[dbo].[ARKALK]

But something is wrong with the Syntax. Any Ideas?

I found out: if I do a 

select Artikelnummer,Listenpreis*1.19 as money from [SL_M03KNE].[dbo].[ARKALK]

i get: 5,59

If i do a

EXEC master..xp_cmdshell 'bcp "select Artikelnummer,Listenpreis*1.19 as money from [SL_M03KNE].[dbo].[ARKALK]" queryout "D:\shop\xml\Artikelpreise_ohne.csv" -E -c -T -x

the bcp do a conversion from the komma to dot. How can i fixed this?

Upvotes: 15

Views: 64985

Answers (5)

Serkan Arslan
Serkan Arslan

Reputation: 13393

Could you try this?

EXEC master..xp_cmdshell 'bcp "select Artikelnummer, REPLACE(CAST( Listenpreis*1.19 AS VARCHAR),''.'','','') as money from [SL_M03KNE].[dbo].[ARKALK]" queryout "D:\shop\xml\Artikelpreise_ohne.csv" -E -c -T -x'

Upvotes: 0

Terka Bone
Terka Bone

Reputation: 1

I struggled with this problem too and to me, the fuction that works is:

CAST(replace_this_with_number_or_column_you_want_to_convert) AS float)

Is that helpful to you too?

Upvotes: 0

Mathew Thompson
Mathew Thompson

Reputation: 56429

You're missing a comma and an apostrophe (you have '.',' and you need '.',','), try:

SELECT 
    REPLACE(CAST(Listenpreis*1.19 as decimal(29,2)), '.', ',')  as Listenpreis
FROM [SL_M03KNE].[dbo].[ARKALK]

Upvotes: 3

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Your as Listenpreis aliasing is in the wrong place. It needs to be the last thing. Also the '.',' part.

SELECT REPLACE(CAST(Listenpreis*1.19 AS DECIMAL(29,2)) ,'.',',')  AS Listenpreis
 FROM [SL_M03KNE].[dbo].[ARKALK]

SQLFiddle DEMO

Upvotes: 20

Chris
Chris

Reputation: 2952

This should work:

select replace(cast(Listenpreis*1.19 as decimal(29,2)),'.',',') as Listenpreis
 from [SL_M03KNE].[dbo].[ARKALK]

It does sound like you're compensating for cultural settings, have a look at the COLLATE statement.

Upvotes: 4

Related Questions