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
REPLACE(list_column, '|', ''',''')replaces each|separator with',':a|b|cbecomesa','b','c.- The leading and trailing quotes are added during concatenation, giving
IN ('a','b','c'). EXECUTE (...)runs the built string.
Notes
- The assignment SELECT must return a single row, otherwise
@INholds the value of the last row read. - Dynamic SQL is open to SQL injection if the source is not trusted; on SQL Server 2016 and later, prefer
STRING_SPLIT(WHERE IP IN (SELECT value FROM STRING_SPLIT(@list, '|'))).