开发者

Break a parameters in SQL and do search

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 t开发者_如何学JAVAhe 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


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))


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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜