In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.
Further, I often use conditions like WHERE something in (1,1,2,3,5,8,13,21) for better readability and flexibility of my SQL statements.
Is there any possible way to combine these two things without writing complicated sub-selects?
I want something as easy as WHERE something LIKE ('bla%', '%foo%', 'batz%') instead of this:
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
I'm working with SQl Server and Oracle here but I'm interested if this is possible in any RDBMS at all.
Answers
In SQL Server and Oracle, you can achieve the functionality you're looking for using the OR operator along with multiple LIKE conditions, as you've already shown. Unfortunately, there isn't a built-in syntax to directly achieve what you're asking for in a single line without using complicated sub-selects.
However, you can simplify the syntax slightly by using the INSTR function in Oracle to check if a string contains any of the substrings you're interested in. Here's an example:
WHERE INSTR(something, 'bla') > 0
OR INSTR(something, 'foo') > 0
OR INSTR(something, 'batz') > 0
This approach doesn't directly use LIKE, but it achieves a similar result by checking if the string contains a specific substring.
Unfortunately, SQL Server doesn't have a similar function like INSTR that directly returns the position of a substring within a string. Therefore, in SQL Server, you'll have to stick with using multiple LIKE conditions or consider more complex solutions involving sub-selects if performance is a concern.
