Break a parameters in SQL and do search

Question

basically I have a string being passed to a variable.. say

@MyParameter =     "Flower Beer Dog" 

so what i want to do is to find all the records that contain "Flower", "Beer" or "Dog"

Somehing like if I said

select * from myTable where myColumn = 'Flower' or myColumn = 'Beer' or myColumn = 'Dog'

but the thing is that I am getting it in the @MyParameter and I am not sure how to split it and and convert it into the query above

Answer 1

This returns a table that displays your delimited string as seperate fields instead. I.e.: "Flower Beer Dog" becomes
Flower
Beer
Dog

The function uses commas to delimit instead of spaces, but you can change that part if you need to.

CREATE Function [dbo].[ParseStringList]  (@StringArray nvarchar(max) )  
Returns @tbl_string Table  (ParsedString nvarchar(max))  As  

BEGIN 

DECLARE @end Int,
        @start Int

SET @stringArray =  @StringArray + ',' 
SET @start=1
SET @end=1

WHILE @end<Len(@StringArray)
    BEGIN
        SET @end = CharIndex(',', @StringArray, @end)
        INSERT INTO @tbl_string 
            SELECT
                Substring(@StringArray, @start, @end-@start)

        SET @start=@end+1
        SET @end = @end+1
    END

RETURN
END

Then you use it like this:

SELECT * 
FROM table
WHERE searchfield In (Select ParsedString From dbo.ParseStringList(@StringArray))

Answer 2

You could use LIKE to do something like this -

select * from myTable 
where '|' + REPLACE(@MyParameter,' ','|') + '|'
like '%|' + myColumn  + '|%'

Technique taken from this question - Parameterize an SQL IN clause

Answer 3

You may want to check out the following post about string splitting in SQL. From there, you can use the returned values to check your table:

How do I split a string so I can access item x?

It is a non trivial problem that can probably be easier solved before the data reaches SQL if at all possible.