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

Question

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

Navigation

#1 by (1 votes)

1

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

On a first try, I just thought about making the clause as follows: [...] WHERE tabela_A.cod_canal IN (tabela_B.canais) [...]

Of course, I got the error:

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 using the PATINDEX function, but I believe I did not understand the correct function 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 of tabela_B from the relationship between the cod_canal and canais fields? Given the difference of the type of data and formats inserted in the respective fields.

sql-server

asked by anonymous 25.08.2016 / 16:08

1 answer

How do I change the style of an element in an iframe from the parent page? TypeError: split () missing 1 required positional argument: 'string'

score 1 · Accepted Answer

You have to convert your string to a list, so you would need a FUNCTION to use 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 call as follows.

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