How can I convert text with a localized number format into a number in MS SQL Server?

Question

Let's say I have a number in the german number format "1,00" which is equal to 1.0 in the en-US locale.

Is there a built-in way to convert this text in T-SQL to the corresponding number? It seems like CONVERT and CAST accept only numbers with '.' as the decimal separator and I see no way to tell them otherwise.

I was thinking about simply replacing ',' with '.' but this gets ugly if I have a german number with thounsands separator like "1.000,00".

Doing the conversion after retrieving the "wrong" SQL result is not an option.

Answer 1

I would recommend you do it at the application level, as the application is aware of the locale. What language/framework are you using?

Answer 2

Seems to be look like solution is already with

http://stackoverflow.com/questions/507477/how-to-convert-a-float-to-a-string-regardless-of-regional-settings

Answer 3

If you want to do this inside SQL Server, then you will have to create a CLR stored procedure/user-defined function which will take the string as a parameter and return the numeric type that you desire. You might also want to take a parameter that indicates what region names (en-US for example) you might want to use to indicate the culture to use for determining the parsing pattern.

Then, in the code, you would call the TryParse method on Double/Decimal/Int32 and use the appropriate CultureInfo to indicate the parse pattern. You can cycle through a bunch of them, or use some other information to determine the right pattern.