Pass comma separated string to stored procedure and apply LIKE to query for each value?

Question

I would like to pass a comma separated string or similar (whatever is best) to a stored procedure and have it apply each value to a LIKE condition in a query.

For example if I pass @terms = "avenue,park,road" to the stored procedure it would then generate a query along the lines of:

SELECT *
FROM properties
WHERE
    properties.Address LIKE @terms[0] OR
    properties.Address LIKE @terms[1] OR
    properties.Address LIKE @terms[2]

It needs to work with SQL server 2008. Can anyone advise me of the best approach? Thanks.

http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns take a look at that.. you can use something similar to split them out and then do your LIKE — Kevin M, Feb 25 '16 at 21:25
i would do something like this using the xml data type.. http://sqlfiddle.com/#!9/9eecb7d/46596 — JamieD77, Feb 25 '16 at 21:54

score 1 · Answer 1 · answered Feb 25 '16 at 21:35

I wrote a function that splits a string and returns it as a table. You can then join onto it like a normal table.

CREATE FUNCTION [dbo].[FN_SPLIT_TBL](@InExp varchar(8000), @Sep varchar(10)) 
RETURNS @Res    TABLE(
    Pos         int,
    Value       varchar(max))
AS
BEGIN
    WITH Pieces(pn, start, stop) AS (
        SELECT 1, 1, CHARINDEX(@Sep, @InExp)
        UNION ALL
        SELECT pn + 1, stop + 1, CHARINDEX(@sep, @InExp, stop + 1)
        FROM Pieces
        WHERE stop > 0
    )

    INSERT INTO @Res
    SELECT pn, SUBSTRING(@InExp, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
    FROM Pieces OPTION (MAXRECURSION 0);

    RETURN;
END

Usage

SELECT b.Field 
FROM DBO.[FN_SPLIT_TBL]('TEST1,TEST2', ',') a
    JOIN YOURTABLE b ON b.Field LIKE a.Value + '%'

Returns

Pos Value
----------
1   TEST1
2   TEST2

That should do the trick

Pass comma separated string to stored procedure and apply LIKE to query for each value?

1 Answers1