SQL query that worked before, does not work anymore

An excel file i’m being supplied daily contains a space in one of the numbers that is supposed to be an integer. This was easily fixed when i simply replaced it via a replace and convert in a query. However, now that i switched from .NET Core to NodeJS, when i tried to use the same queries, it didn’t go as expected. Here is the query i’m working with.

"INSERT INTO table SELECT ANvarChar, CONVERT(INT, replace(AVarchar,' ', '')), AnotherVarCharFROM TempImport"

When i ran it through C#, targetting the internal SQL Server database worked, but now i’m doing the same on linux, using mssql server.

"BULK INSERT TempImport FROM '" + csvFilePath + "' WITH (FIELDTERMINATOR = '||', ROWTERMINATOR = 'n');"

It throws the error:

RequestError: Conversion failed when converting the varchar value ‘AVarchar’ to data type int.

It’s clear that the convert and replace don’t work in this context, What could i do instead? I already tried doing it via the converter, and during the download, without any luck.

Answer

The character could be something other than space after replacement. You can check if the data contains space after replacement like this:

SELECT * FROM (
SELECT ANvarChar, replace(AVarchar,' ', '') as TEST, AnotherVarChar FROM TempImport
) as Conv
WHERE TEST LIKE '% %'

This should not return any rows. In this case other whitespace replacements should be added (like one adviced by Zeki Gumus in the comment).

Advertisement

Answer