Here is a splitter TVF that returns each data value as a row in a table. It relies on some SQL XML magic.

/*

USAGE:

SELECT [Value] FROM [dbo].[split_delimited_string]

('1||2||3||4||5||6||7||8||9||10||11||12||13||14||15||16||17||18||19||20','||')

WHERE Value IN (1,2,3,4,5,6,7,8,9,20)

SELECT [Value] FROM [dbo].[split_delimited_string]

('1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20',';')

SELECT [Value] FROM [dbo].[split_delimited_string]

('1[][]2[][]3[][]4[][]5[][]6[][]7[][]8[][]9[][]10[][]11[][]12[][]13[][]14[][]15[][]16[][]17[][]18[][]19[][]20','[][]')

*/

CREATE FUNCTION [dbo].[split_delimited_string]

(

@STR NVARCHAR(MAX),

@sep NVARCHAR(MAX)

)

RETURNS @value TABLE (Value NVARCHAR(MAX))

AS

BEGIN

DECLARE @xml XML

SELECT @xml = CONVERT(XML,'<r>' + REPLACE(@str,@sep,'</r><r>') + '</r>')

INSERT INTO @value(Value)

SELECT t.value('.','NVARCHAR(MAX)')

FROM @xml.nodes('/r') AS x(t)

RETURN;

END