'Error converting varchar to number 123,456

I am trying to do a bulk insert from csv into a SQL Server database. Here is a snippet of a part of the query.

The data for this column in the CSV is in the format 123,345 with commas (it could also be null, or without any digit separator), I extract it as a string, and I am trying to replace the comma with nothing and cast it to a integer, in the format file the column is

<COLUMN SOURCE="10" NAME="abc" xsi:type="SQLVARYCHAR"/>

And the query is of the form

SELECT 
    CAST(REPLACE(abc, '','') AS NUMERIC) abc
FROM 
    OPENROWSET(BULK ''' + @myfile + ''',' +
               'FORMATFILE = ''' + @fomatfile + ''',' +
               'FIRSTROW = 0,' +
               'ROWS_PER_BATCH =' + CAST(@size as VARCHAR(4)) + '

But I get this error

Error converting varchar to numeric

for this particular column..

sql-servermssql-jdbc


Solution 1:[1]

You have an error in your REPLACE function, you should be replacing a ',' with an empty string '', whereas you are replacing an empty string '', with an empty string ''.

DECLARE @s varchar(10) = '123,456';

SELECT  CAST(REPLACE(@s, ',', '') AS NUMERIC) abc
GO

|    abc |
| -----: |
| 123456 |

db<>fiddle here

Solution 2:[2]

One option is to change your import column to MONEY. It tends to be a little more forgiving.

Example

Select AsMoney  = try_convert(money,'123,345')
      ,AsInt    = try_convert(int,'123,345')
      ,AsDecimal= try_convert(decimal(15,2),'123,345')

Results -- Notice MONEY will convert

AsMoney     AsInt   AsDecimal
123345.00   NULL    NULL

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Steve Ford
Solution 2 John Cappelletti

Related Questions