How to delimit a string field for use in the IN clause

Question

In the tabela_A own a field cod_canal where is a INT, in tabela_B own a field canais of the string type and separating the codes by ,. Example: 1,3,6,9,12.

On a first attempt, I just thought I’d make the clause this way: [...] WHERE tabela_A.cod_canal IN (tabela_B.canais) [...]

And obviously, I got the mistake:

SQL Error [245] [S0001]: Conversion failed when Converting the nvarchar value '0,1,3,5,9,12' to data type int.
com.microsoft.sqlserver.jdbc.Sqlserverexception: Conversion failed when Converting the nvarchar value '0,1,3,5,9,12' to data type int.

I tried to use the function PATINDEX, but I believe I have not understood the correct functioning of it and I do not have the expected result

AND PATINDEX('%' + CAST(tabela_A.cod_canal AS NVARCHAR) + '%', tabela_B.canais) > 0

How can I get the results from tabela_B from the relationship between the fields cod_canal and canais? Due to the difference in the type of data and formats inserted in the respective fields.

Answer 1

You have to convert your string to a list, for that you would need a FUNCTION to be used within your select.

create FUNCTION [dbo].[fnStringList2Table]
(
    @List varchar(MAX)
)
RETURNS 
@ParsedList table
(
    item int
)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            INSERT INTO @ParsedList (item) 
            VALUES (CAST(@item AS int))
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN
END

And flame as follows.

SELECT      *
FROM   tabela_A.cod_canal IN (SELECT item from fnStringList2Table(@statuslist))