LLI Wiki — My Digital Knowledge Base

SQL Server: use a pipe-delimited list in an IN clause with REPLACE

Turn an a|b|c string into an IN ('a','b','c') list using REPLACE and dynamic SQL.

When a column holds a list of values separated by | (for example IP addresses) and you want to use them in an IN (...) clause, you can turn the string into a SQL list with REPLACE and run the query as dynamic SQL.

DECLARE @IN varchar(200)
SELECT @IN = REPLACE(list_column, '|', ''',''')
FROM source_table
WHERE some_condition

EXECUTE ('SELECT cols FROM target_table WHERE IP IN (''' + @IN + ''')')

How it works

Notes

Mastodon LLIMastodon SocialFosstodon