asked on

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

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?

Guy Hengel [angelIII / a3]

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

Cheers

Guy Hengel [angelIII / a3]

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

grishav

ASKER

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.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

grishav

ASKER

Thanks