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

Question

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?

Answer 1

(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

Answer 2

What version of SQL Server are you using? From SQL Server 2012 onwards you can use TRY_PARSE with its USING culture argument. You can also use PARSE, the difference being PARSE will fail if the conversion fails and TRY_PARSE will return a NULL, eg

DECLARE @t TABLE ( x VARCHAR(10) )
INSERT INTO @t
VALUES ( '7.000,45' ), ( 'xxx' )
SELECT x, 
 TRY_PARSE( x AS NUMERIC(10,2) USING 'El-GR' ) x
FROM @t

test results

HTH

Answer 3

The following code worked in my case:

select convert(varchar,FORMAT(123456789.0258,'###,###,###.00','de-de'))

output: 123.456.789,03

• Shahed Adnan: or select convert(varchar,FORMAT(cast(yourValue as numeric(10,2)),'###,###,###.00','de-de'))
• ypercubeᵀᴹ: it seems you are answering the reverse problem of what the OP asked. they don't want to format numeric values, they want to convert formatted varchar values to numeric.
• Solomon Rutzky: Also, two notes: 1) always specify a length for VARCHAR, NVARCHAR, CHAR, and NCHAR types. The default is 30 in some case and 1 in other cases, which makes for unmaintainable / error-prone code. 2) you don't need the exact layout when using #. You just need one of them (e.g. #) for all digits but no thousands separator, or two of them separated by a comma (e.g. #,#) to get all digits and the thousands separator. Hence, SELECT CONVERT(VARCHAR(20), FORMAT(123456789.0258, N'#,#.00', N'de')); returns the same output as what you are suggesting.