SQL Server: Combining wildcard searches with multiple patterns

Data and Databases
1

I’m trying to figure out how to do a search in SQL Server that combines wildcards with multiple patterns. Is there a way to mix LIKE and IN operators?

Here’s what I’m aiming for:

SELECT * FROM products WHERE name MATCHES ('Fruit%', 'Veg%', 'Dairy%', '%Fresh%')

This should find stuff like:

  • Fruit
  • Fruit salad
  • Vegetables
  • Vegetable soup
  • Dairy products
  • Farm Fresh Eggs

Can SQL Server do this kind of search? If so, how? I’ve tried a few things but can’t get it to work right. Any help would be great!

2

While the previous suggestions are valid, you might find it more efficient to use a pattern-matching function like PATINDEX. Here’s an approach that could work well:

SELECT * FROM products
WHERE PATINDEX(‘Fruit%|Veg%|Dairy%|%Fresh%’, name) > 0

This method uses a single PATINDEX function with a pattern that combines all your search criteria. The ‘|’ acts as an OR operator within the pattern. It’s generally faster than multiple LIKE comparisons, especially for larger datasets. Additionally, it’s more maintainable as you can easily add or remove patterns without changing the structure of your query.

3

hey, interesting query. have you tried using charindex? like:

SELECT * FROM products WHERE CHARINDEX(‘Fruit’,name)=1 OR CHARINDEX(‘Veg’,name)=1 OR CHARINDEX(‘Dairy’,name)=1 OR CHARINDEX(‘Fresh’,name)>0

would that work for you? any thoughts?

4

yep, u can do that with a combo of LIKE and OR. Try this:

SELECT * FROM products WHERE
name LIKE ‘Fruit%’ OR
name LIKE ‘Veg%’ OR
name LIKE ‘Dairy%’ OR
name LIKE ‘%Fresh%’

that should grab everything ur lookin for. hope it helps!