Convert string numeric values with comma as decimal separator to NUMERIC(10, 2)

I have an SQL table of varchar columns which contain Greek formatted numbers (. as thousand separator and comma as decimal separator)

The classic conversion

CONVERT(numeric(10,2),REPLACE([value],',','.'))

does not work because the . (thousand separator) kills the conversion

E.g try

CONVERT(numeric(10,2),REPLACE('7.000,45',',','.'))

I want to convert such values to numeric(10,2)

Any suggestions of how to handle it?

(If you are using SQL Server 2012 or newer, please see @wBob's answer for a cleaner approach. The approach outlined in my answer below is only required if you are using SQL Server 2008 R2 or older.)

You don't need (or want) the thousands' separator when converting to NUMERIC, regardless if it is comma, period, or space, so just get rid of them first. Then convert the comma into a period / decimal and you are done:

SELECT CONVERT(NUMERIC(10, 2),
REPLACE(
REPLACE('7.000,45', '.', ''),
',', '.'
)
) AS [Converted];


Returns:

7000.45


For the sake of completeness, I should mention that I also tried:

-
SET LANGUAGE Greek;

-
Looking at various format styles for CONVERT, but nothing applies here.

-
The FORMAT function, but the input type must be a numeric or date/time/datetime value (that and it was introduced in SQL Server 2012, so not applicable to SQL Server 2008 R2 or older).

And nothing else seemed to work. I was hoping to find something more elegant than two REPLACE calls, but so far no such luck.

Also, just to mention, while not a pure T-SQL solution, this can also be accomplished via SQLCLR. And, there is a pre-done function that does this in the SQL# library (that I wrote) named String_TryParseToDecimal. This function is available in the Free version, and works in every version of SQL Server starting with SQL Server 2005:

SELECT SQL#.String_TryParseToDecimal('7.000,45', 'el-GR');


Returns:

7000.45000000000000000000


StackExchange Database Administrators Q#118057, answer score: 14