TSQL - Partial Matching using LIKE for multiple va

问题:

I would like to seek your advice whether any function in SQL server that allow me to perform partial matching for a list of values ?

The entire string that need to be matched will be passed in via store procedure.

I am trying to find other alternative before writing my own function to split the string by comma and then union all the results before return the data to the program.

For example, I would pass in the following string into my TSQL

apple,orange,pear

in my WHERE clause 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 rather than writing function to break each string ?

Data in my Table

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

So, when I passed in the string "apple,pear" , I need to return

apple red
apple green
pear big
pear small

回答1:

You can create a temp table as

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

Side note: Make sure you check if the temp table exists to avoid errors. Now you can insert your search words to the temp table as

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

Now using this temp table, Search your table using a INNER JOIN like

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

As a note, Temp Tables are something I try to avoid mostly, but here it comes handy, and the case I was using this solution was not demanding on performance. Rather it made it easier to keep the ever growing searchItems maintained.

Hope this works for others too.

回答2:

It could be as simple as:

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

回答3:

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

回答4:

Found an answer here on how to use XML to split.

I'll just insert the values into a table and then query it.

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

回答5:

Try this (Full-Text Searching):

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

And here is the help to implement:

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

回答6:

There is a way to do it in SQL, but it is quite complicated. Assuming you can live with matching a string of up to, say, three fruit names, you proceed in the following manner.

We assume that @fruits is the varchar variable containing the list of fruits, to which we append 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 we will need for the 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 set of equations for p2, v2, and r2. And likewise, we substitute those second set of values into the third set, and so on. We end up with these monstrosities 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 expanded, the query is:

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 far as we like. But each further vN value gets far more complicated than the previous.

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