Using "LIKE"/"IN" to mach multiple patterns in SQL?

Question

I'd like to search for multiple patterns in a table:
That is,
select * from my_table where name like ('ABC%','DEF%',...);

I know the above query is not possible. But how do I write a query to select rows that match multiple patterns.
I know we cant use "IN" since it needs exact matches. LIKE only takes a single pattern. I dont want to use sub queries since the input from the user will be dynamic(any number of names)- so I do not want to hardcode the query. Any ideas?

Answer 1

SELECT * FROM my_table

WHERE name LIKE 'ABC%'

OR name LIKE 'DEF%'

OR name LIKE 'XYZ%'



If you have MySQL, you can use regexps, which can be put into your SQL dynamically:



SELECT * FROM my_table WHERE

name REGEXP '^(ABC|DEF|XYZ)'

Answer 2

You might also try something like:



SELECT * FROM my_table

WHERE LEFT(name,3) in ('ABC','DEF','XYZ')



This is suitable for adhoc queries of smaller tables--this is about simpler typing and not performance.



For larger tables or something you intend to run a lot (like inside your app or an SP), General Cucombre's answer will run best. Most systems translate IN statements into the equivalent of a bunch of OR statements anyway. You might also get the advantage of a range seek instead of a table scan. Few systems (if any) will do a seek when a string function is in the WHERE clause; the function processing will slow things down too.

Answer 3

There is a different answer, depending on whether you mean generally or just for data retrieval. If you mean for data retrieval: Simple queries involving a few tables can be done with one query. But when you need to join lots of tables, or do more complex queries on large datasets then it is sometimes necessary to split the job and store an intermediate result between steps. You would need to create a table to put these results in. If you mean generally: Tables are structured lists of things that you are interested in, so you usually create tables when - you are building a new information system, or - extending an existing system to handle details of things it didn't have before For example, if you had a small business, your "information system" might start out as a simple list of your customers and their orders. This could just be two tables linked by a customer reference As your business grows you might decide you need to track other "things" : supplier purchases, payments in/out, employees, inventory. For each new "type" of thing to track, you would need a new table. The CREATE command can be used to control data access, speed up data retrieval or make use of the data (eg automatically print invoices from the data in the customer and order tables). But that is another question!