I’m trying to find a way to search for multiple patterns in SQL Server. Is there a method to mix wildcard searches with a list of options? For example, I want to match strings that start with ‘Book’, ‘Page’, or ‘Chapter’, or contain ‘Index’ anywhere.
Here’s what I’m thinking, but I know it’s not correct syntax:
SELECT * FROM library WHERE title MATCHES ('Book%', 'Page%', 'Chapter%', '%Index%')
This should find entries like:
- BookClub
- PageTurner
- ChapterOne
- IndexFinder
- TheIndexer
Can anyone help me figure out the right way to do this in SQL Server? I’ve tried a few things but can’t seem to get it working. Thanks!
try using the like operator with or conditions:
SELECT * FROM library
WHERE title like 'Book%' or title like 'Page%' or title like 'Chapter%' or title like '%Index%'
should work for your query.
ooh, interesting question! have you considered using a regex pattern? something like this might work:
SELECT * FROM library WHERE title LIKE ‘%[BPC][aho][ogn][ke][%]%’ OR title LIKE ‘%Index%’
it’s a bit tricky, but could be more flexible. what do you think? have you tried any other approaches?
You’re on the right track with your approach, but SQL Server doesn’t have a direct ‘MATCHES’ function. Instead, you can use the LIKE operator combined with OR conditions to achieve what you’re looking for. Here’s how you can structure your query:
SELECT * FROM library
WHERE title LIKE ‘Book%’
OR title LIKE ‘Page%’
OR title LIKE ‘Chapter%’
OR title LIKE ‘%Index%’
This query will return all the examples you mentioned. It’s a bit more verbose than your initial idea, but it’s the standard way to handle multiple pattern matching in SQL Server. If you find yourself needing to do this frequently, you might consider creating a function to simplify the process for future use.