Error converting varchar to number 123,456-CodePudding

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..

CodePudding user response：

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

CodePudding user response：

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