Is there a combination of "LIKE" and "IN" in SQL?
Solution 1
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative.
Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different:
Oracle:
WHERE CONTAINS(t.something, 'bla OR foo OR batz', 1) > 0
SQL Server:
WHERE CONTAINS(t.something, '"bla*" OR "foo*" OR "batz*"')
The column you are querying must be full-text indexed.
Reference:
Solution 2
If you want to make your statement easily readable, then you can use REGEXP_LIKE (available from Oracle version 10 onwards).
An example table:
SQL> create table mytable (something)
2 as
3 select 'blabla' from dual union all
4 select 'notbla' from dual union all
5 select 'ofooof' from dual union all
6 select 'ofofof' from dual union all
7 select 'batzzz' from dual
8 /
Table created.
The original syntax:
SQL> select something
2 from mytable
3 where something like 'bla%'
4 or something like '%foo%'
5 or something like 'batz%'
6 /
SOMETH
------
blabla
ofooof
batzzz
3 rows selected.
And a simple looking query with REGEXP_LIKE
SQL> select something
2 from mytable
3 where regexp_like (something,'^bla|foo|^batz')
4 /
SOMETH
------
blabla
ofooof
batzzz
3 rows selected.
BUT ...
I would not recommend it myself due to the not-so-good performance. I'd stick with the several LIKE predicates. So the examples were just for fun.
Solution 3
you're stuck with the
WHERE something LIKE 'bla%'
OR something LIKE '%foo%'
OR something LIKE 'batz%'
unless you populate a temp table (include the wild cards in with the data) and join like this:
FROM YourTable y
INNER JOIN YourTempTable t On y.something LIKE t.something
try it out (using SQL Server syntax):
declare @x table (x varchar(10))
declare @y table (y varchar(10))
insert @x values ('abcdefg')
insert @x values ('abc')
insert @x values ('mnop')
insert @y values ('%abc%')
insert @y values ('%b%')
select distinct *
FROM @x x
WHERE x.x LIKE '%abc%'
or x.x LIKE '%b%'
select distinct x.*
FROM @x x
INNER JOIN @y y On x.x LIKE y.y
OUTPUT:
x
----------
abcdefg
abc
(2 row(s) affected)
x
----------
abc
abcdefg
(2 row(s) affected)
Solution 4
With PostgreSQL there is the ANY
or ALL
form:
WHERE col LIKE ANY( subselect )
or
WHERE col LIKE ALL( subselect )
where the subselect returns exactly one column of data.
Solution 5
Another solution, should work on any RDBMS:
WHERE EXISTS (SELECT 1
FROM (SELECT 'bla%' pattern FROM dual UNION ALL
SELECT '%foo%' FROM dual UNION ALL
SELECT 'batz%' FROM dual)
WHERE something LIKE pattern)
The inner select can be replaced by another source of patterns like a table (or a view) in this way:
WHERE EXISTS (SELECT 1
FROM table_of_patterns t
WHERE something LIKE t.pattern)
table_of_patterns
should contain at least a column pattern
, and can be populated like this:
INSERT INTO table_of_patterns(pattern) VALUES ('bla%');
INSERT INTO table_of_patterns(pattern) VALUES ('%foo%');
INSERT INTO table_of_patterns(pattern) VALUES ('batz%');
selfawaresoup
Updated on April 06, 2022Comments
-
selfawaresoup about 2 years
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.
-
Cosmic Hawk over 8 yearsYou have to do and like or: AND (something LIKE '%thing%' or something LIKE '%thing%' or something LIKE '%thing%')
-
William Robertson over 6 yearsI wish we had Teradata's
like any
/like all
: stackoverflow.com/questions/40475982/sql-like-any-vs-like-all. (For the record, this has been requested on the Oracle Community Ideas forum community.oracle.com/ideas/11592) -
Mark Schultheiss over 4 years
-
safay almost 3 yearsSnowflake also supports LIKE ANY docs.snowflake.com/en/sql-reference/functions/like_any.html
-
-
selfawaresoup almost 14 yearsOk, this would work, but it's not going into my intended direction of making the SQL statement more easily readable :)
-
KM. almost 14 yearsin SQL you go for index usage and performance. Only use indenting and naming for SQL readability, when you make other modifications for readability only you risk changing the execution plan ( which affects index usage and performance). If you are not careful, you can easily change an instantly running query to a very slow one by making trivial changes.
-
Philip Kelley almost 14 yearsThe first statement of this answer is key -- (most?) SQL-based systems and languages don't support what you want, not without implementing work-arounds. (In SQL server, would Full Text indexing help?)
-
KM. almost 14 years@Philip Kelley, can SQL Server's Full Text indexing do
LIKE 'bla%'
, which in the OP's example code? or can in only doLIKE '%bla%'
searches? -
DCookie almost 14 years+1 nice illustration of REGEXP usage in 10g. I'm curious, though, if performance would really be all that much worse. Both will require full table and/or index scans, no?
-
Rob van Wijk almost 14 yearsTrue. But regular expressions burn CPU like crazy, not I/O. If it is worse and how much worse it is, depends on how large your list of expressions is and whether the column is indexed or not, among others. It is just a warning, so that the original poster is not surprised when he starts implementing it.
-
Philip Kelley almost 14 yearsI honestly don't know, I've never used FT indexing. I tossed it in as a sample of a possible work-around that's already included in the product. For what he's doing (A or B or C), I suspect it doesn't do it, am fairly confident that it'd take a lot of effort to determine this, and know that its outside the scope of his original question (does SQL do it natively).
-
selfawaresoup almost 14 yearsWell, that's exactly what I'd like to avoid. Although it works.
-
RichardTheKiwi over 13 years+1 for joining to table, but don't use temp, just derive it (select A union all select B... )
-
Pierre-Gilles Levallois almost 12 yearsHi, with Oracle, you need to build plaintext indexes on the columns you want to apply "CONTAINS" operator. Depending of your data volume this could be quite long.
-
Marcel over 11 yearsWith SQL Server (at least the 2008 version) the comment of @Pilooz does apply too, you need to build full text indexes.
-
dburges almost 11 yearsThat's interesting. However, be aware that this should only be used on a smal table as the like statement can't use indexes. This is why the full text search, while harder to intially set up, is the better choice if you have alot of data.
-
Phil Factor over 10 yearsWhy avoid this solution? It works as fast as the accepted solution, and is just as versatile.
-
Darius X. about 9 yearsHow's that going to work? The LHS is a string with a %, and that % is therefore not a wildcard
-
Jakub Kania almost 9 years@PhilFactor This solution can create duplicate rows.
-
Assad Ebrahim over 8 yearsAre
LIKE ANY
andLIKE ALL
common to all SQL dialects, i.e. part of the core language, or specific to a dialect? -
Benoit over 8 years@AssadEbrahim, no they are specific. Oracle has
= ANY
or<> ALL
but it works only in SQL, not in PLSQL for example. -
DeeArgee over 8 yearsThe inner join and like is a nice trick, I've used
inner join sometable st on st.column like '%' + t.othercolumn + '%'
-
ShoeLace over 7 yearsone issue with this approach is you loose the ability to use an index on t1.something if it exists..
-
ypercubeᵀᴹ about 7 yearsI think this is standard syntax (but not many DBMS have implemented it)
-
mik about 6 yearsA row will be duplicated if matched by many conditions at once.
-
mik about 6 yearsA row will be duplicated if matched by many conditions at once.
-
mik about 6 yearsInner join will duplicate rows that match many conditions at once.
-
Fandango68 almost 6 yearsBut it's uglier than a set of OR statements
-
mik almost 6 years@Fandango68, but the union of selects can be replaced by another source of patterns like a table, a view, etc.
-
rogerdpack over 5 yearsFor postgres see stackoverflow.com/questions/2245536/…
-
mik over 4 yearsthis will never find 'batz'
-
The Fool over 4 yearsThank you. This should be the accepted answer IMO. Not everyone has a defined full text index (whatever that means) Your first suggestions works like a charm. You can even put the wildcards in the temp table values itself instead of concatenating on the LIKE.
-
ᴍᴀᴛᴛ ʙᴀᴋᴇʀ about 4 yearsMaximum length is 4000.
-
Code Novice almost 4 yearsIn the event anyone is interested here is an example of syntax to add additional columns when using VALUES: SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
-
Antoine Pelletier over 3 years@mik that's what the "distinct" key word is for, eliminating duplicates. This really is the finest SQL oriented solution to the problem. Putting the list of possible matches in a temporary table is nice because you can fill it using different conditions. This is a dynamic approach, just like it is supposed to be.
-
mik over 3 years@AntoinePelletier using EXISTS instead of INNER JOIN would eliminate the need for the extra distinct
-
Antoine Pelletier over 3 years@mik Yes it's another good, maybe better way. Just saw your answer and, it could be improved a bit. It appears non-dynamical and that's the only reason why it didn't receive my attention. Like you said in the comments, unions could be replaced by a temporary table. But it's nice to see it. If your request was complete (including the first SELECT) and used a dynamic approach (temp table) I would certainly upvote it and maybe consider it the best answer...