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.
精彩评论