how can I convert number to string using variable thousand or decimal separators (comma vs period) ?

grishav used Ask the Experts™
Hello all,
I have an international DB, and I want to represent the numbers in it in different ways for different users.
For example, the number 123,456.789 may be displayed as 123.456,789 or as 123 456, 789 in different locales.
I tried to use UDF that parses the number in a loop and generates a string - takes number modulo 1000, adds thousand separator, etc. - but this is not fast enough, unfortunately...
Is there a way to define converting rules for CONVERT/CAST function?
Or maybe some other function that I can't find?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

While I would do that kind of conversion in the client application, you could do this:

declare @c numeric(20,8)
set @c = 1234567.890
select @c
, convert(varchar,convert(money, @c),1)
, replace(replace(replace(convert(varchar,convert(money, @c),1),',','X'),'.',','),'X','.')
, replace(convert(varchar,convert(money, @c),1),',',' ')
, replace(replace(convert(varchar,convert(money, @c),1),',',' '),'.',',')

However, the money data type is limited, so you might not be able to use that in all cases.

If the suggestion is not enough, i will try to work out something else

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

PS: my output (reformatted):
1 234 567.89
1 234 567,89
(1 row(s) affected)


This solution is good, but converting to money rounds the number to 2 digits after the decimal point...
For example, it will not work with @c = 1234567.888
P.S. I need to use this expression in SELECT clause of INSERT..SELECT, so I don't think that passing the result of SELECT to the client application, converting the numbers to string there and then passing the result back to server for the INSERT would be a good solution.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
ok, let's split the job into 2 parts:

declare @v_variable numeric ( 20,10)
set @v_variable = 123456789.123456789
select @v_variable
, replace(convert(varchar,cast(cast(@v_variable as numeric(20,0)) as money),1),'.00','')
. @v_variable - cast(@v_variable as numeric(20,0))

My output:                                                                                                              

Now, you can easily replace either the "," by "." or " " in the part before the decimal...




Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial