TSQL - partial matching using LIKE for multiple values

Question

I would like to get your advice on whether there is any function on the SQL server that allows me to perform partial matching for a list of values?

The entire string to be matched will be passed through the storage procedure.

I am trying to find another alternative before writing my own function to separate a line by a comma, and then combine all the results before returning the data to the program.

For example, I would pass the following line to my TSQL

apple, orange, pear

in my WHERE it should match

 select * from store where fruits like 'apple%' select * from store where fruits like 'orange%' select * from store where fruits like 'pear%' 

Can I achieve the above results in a single SQL statement, and not write a function to break each line?

Data in my table

 apple red apple green orange sweet orange sour pear big pear small 

So, when I went through the line "apple, pear", I need to return

 apple red apple green pear big pear small 

Answer 1

You can create a temporary table as

 'CREATE TABLE #Pattern ( SearchItems VARCHAR(20) );' 

Note: make sure that a temporary table exists to avoid errors. Now you can insert search words into the pace table as

 'INSERT INTO #Pattern VALUES ('% APPLE %'), ('% ORANGE %'), ('% BANANA %');' 

Now, using this temporary table, find your table using INNER JOIN as

 'SELECT * FROM Store INNER JOIN #Pattern ON Store.Fruits LIKE SearchItems ' 

As a side note, Temp tables are something I try to avoid mostly, but this is convenient here, and the case when I used this solution did not require performance. Rather, it made it easier to support the ever-growing search features.

Hope this works for others too.

Answer 2

Try this, but performance will not be great

 declare @parm varchar(200) set @parm = ','+'apple,orange,pear'+',' select * from store where charindex(fruit,@parm) > 0 

Answer 3

It could be that simple:

 SELECT * FROM store WHERE fruits LIKE 'apple%' OR fruits LIKE 'orange%' OR fruits LIKE 'pear%' 

Answer 4

Found the answer here how to use XML for separation.

I simply insert the values ​​into the table and then query it.

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx

Answer 5

Try this (full text search):

 SELECT * FROM store WHERE CONTAINS(fruits , '"apple*" OR "orange*" OR "pear*"') 

And here is the help for the implementation:

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Answer 6

There is a way to do this in SQL, but it is rather complicated. Assuming you can live with a string match up to, say, three fruit names, you do as follows.

Suppose that @fruits is a varchar variable containing a list of fruits to which we add more comma delimiters (in case it contains less than three fruit names):

 declare @fruits varchar(80); set @fruits = <list of fruits passed in> + ',_,_,_,'; 

The following equations are not SQL, but the math behind the substring operations that we will need for like expressions:

 NOTE: NOT SQL First fruit word: p1 = charindex(',', @fruits) << position of ',' delimiter v1 = substring(@fruits, 0, p1-1) + '%' << fruit word we seek r1 = substring(@fruits, p1+1) << remainder of string Second fruit word: p2 = charindex(',', r1) v2 = substring(r1, 0, p2-1) + '%' r2 = substring(r1, p2+1) Third fruit word: p3 = charindex(',', r2) v3 = substring(r2, 0, p3-1) + '%' r3 = substring(r2, p3+1) ...and so on... 

Now we substitute the first values ​​of p1 , v1 and r1 into the second system of equations for p2 , v2 and r2 . In the same way, we replace the second set of values ​​with the third set, etc. We get these monsters for v1 , v2 and v3 :

 v1 = substring(@fruits, 0, charindex(',', @fruits)-1) + '%' v2 = substring(substring(@fruits, charindex(',', @fruits)+1), 0, charindex(',', substring(@fruits, charindex(',', @fruits)+1))-1) + '%' v3 = substring(substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1), 0, charindex(',', substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1))-1) + '%' 

These are the first three like values ​​we need to look for:

 select * from fruits where fruit like <v1> or fruit like <v2> or fruit like <v3> 

Fully extended query:

 select * from fruits where fruit like substring(@fruits, 0, charindex(',', @fruits)-1) + '%' or fruit like substring(substring(@fruits, charindex(',', @fruits)+1), 0, charindex(',', substring(@fruits, charindex(',', @fruits)+1))-1) + '%' or fruit like substring(substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1), 0, charindex(',', substring(substring(@fruits, charindex(',', @fruits)+1), charindex(',', substring(@fruits, charindex(',', @fruits)+1))+1))-1) + '%' 

We can do more work to extract the 4th word, 5th word, 6th word and so on as we like. But each vN value becomes much more complicated than the previous one.

Note. I have not tried this solution, I only mathematically proved it.