Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.7k points)

Combination of ‘In’ and ‘LIKE’ doesn’t exist in SQL, PLSQL and TSQl. Because, users prefer to use FTS i.e. Full text Search instead of their combination.

Oracle and SQL Server FTS implementations use CONTAINS keyword. But, the syntax is different in both of them.

In SQL Server:

WHERE CONTAINS(t.something, '"bla*" OR "batz*" OR "foo*" ')

In Oracle:

WHERE CONTAINS(t.something, 'bla OR batz OR foo ', 1) > 0

Reference:

Related questions

0 votes
1 answer
0 votes
1 answer
asked Jul 4, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
...