Split column and select value

Question

I have a column in my input data which contains multiple values seperated by a comma. Example:

[my_list] '1,123,233,400,500'

I want to select the rows where [my_list] contains '123'.

What I have tried is:

new SQL.ARRAY(my_list.Split(',')) == '123'

'123' IN my_list.Split(','))

But no success. I'm not really sure how to solve it. Could someone help out?

Answer 1

@table = 
SELECT * FROM 
    ( VALUES
    (1, "1,123,233,400,500"),
    (2, "123,233,400,500"),
    (3, "1,123"),
    (4, "1,233,400,500"),
    (5, "1,   123   ,233,400,500")
    ) AS T(id, my_list);

@result1 =
    SELECT * FROM @table
    WHERE my_list LIKE "%123%";

@result2 =
    SELECT * FROM @table
    WHERE new SQL.ARRAY<string>(my_list.Replace(" ", "").Split(',')).Contains("123");