Synapse Analytics query fails when arguments for WHERE-IN operator contains colon like ('asdf:asdf')

Question

I am using Synapse Analytics to query a simple .csv file containing a single column. Here is the query I'm using:

SELECT a.[Product Code]
    FROM OPENROWSET(
         BULK ('https://<mystorage>.dfs.core.windows.net/<somefolder>/<filename>.csv'),
                  FORMAT = 'CSV',
                  PARSER_VERSION = '2.0',
                  HEADER_ROW = TRUE
              ) as a 
    WHERE a.[Product Code] NOT IN ('123:123', '234:234234')

This query is quite straightforward, so I'm not sure what could go wrong. However, I encountered an error while executing it:

Error converting data type varchar to bigint.

After some investigation, I discovered that the issue is caused by the colon symbol (':') in the parameters.

I attempted to resolve the error by casting the parameter to VARCHAR or NVARCHAR, but this didn't help.

I also couldn't find any ways to escape the colon symbol.

Consequently, I resorted to replacing the colon with a different symbol, like this:

SELECT a.[Product Code]
    FROM OPENROWSET(
         BULK ('https://<mystorage>.dfs.core.windows.net/<somefolder>/<filename>.csv'),
                  FORMAT = 'CSV',
                  PARSER_VERSION = '2.0',
                  HEADER_ROW = TRUE
              ) as a 
WHERE REPLACE(a.[Product Code],':',';') NOT IN ('481154559;asdfa')

Folks, do you have any suggestions for a better approach to handle this issue?

Answer 1

You can manually specify the type of [Product Code] as nvarchar(100). Refer the below code.

Code

SELECT
*
FROM
OPENROWSET(
BULK  'https://***.dfs.core.windows.net/***/***.csv',
FORMAT = 'CSV',
PARSER_VERSION = '2.0',
HEADER_ROW =TRUE
)
with
(
 [Product Code] nvarchar(100)
)
AS [result]
where [Product Code] NOT  IN ('123:123', '234:234234')