TSQL - Partial Matching using LIKE for multiple values

Question

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

Solution 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.

Solution 2

It could be as simple as:

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

Solution 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

Comments

[user3015739]

It didn't work because you can't have apple,orange in the same data. I am just using pattern matching to find all apple red, apple green, orange big, orange small.

[user3015739]

The string will be passed in from a store procedure and I need to return the data set using pattern matching. I have no problem writing a function to split the string and then union the results. Just try to explore what other options I have.

[Sparky]

Sorry, type - I reversed parameters. Try the changed version

[jabu.hlong]

I like simple solutions

[ashleedawg]

"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view xxx.yyy' because it is not full-text indexed."