9

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?

8

(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
8

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

  • 1
    I just added a note to the top of my answer, directing readers here if they are using SQL Server 2012 or newer. – Solomon Rutzky Oct 15 '15 at 2:05
0

The following code worked in my case:

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

output: 123.456.789,03

  • or select convert(varchar,FORMAT(cast(yourValue as numeric(10,2)),'###,###,###.00','de-de')) – Shahed Adnan May 30 '18 at 16:18
  • 2
    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. – ypercubeᵀᴹ May 30 '18 at 16:18
  • 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. – Solomon Rutzky May 30 '18 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.