Using LIKE in an Oracle IN clause

Question

I know I can write a query that will return all rows that contain any number of values in a given column  like so   Select   from tbl where my col in  val1  val2  val3     valn    but if val1  for example  can appear anywhere in my col  which has datatype varchar 300   I might instead write   select   from tbl where my col LIKE   val1     Is there a way of combing these two techniques  I need to search for some 30 possible values that may appear anywhere in the free-form text of the column    Combining these two statements in the following ways does not seem to work   select   from tbl where my col LIKE    val1      val2     val3           select   from tbl where my col in    val1      val2     val3

User · Answer

This one is pretty fast    select   from listofvalue l  inner join tbl on tbl mycol like        l value

User · Answer

A REGEXP LIKE will do a case-insensitive regexp search   select   from Users where Regexp Like  User Name   karl anders leif   i     This will be executed as a full table scan - just as the LIKE or solution  so the performance will be really bad if the table is not small  If it s not used often at all  it might be ok   If you need some kind of performance  you will need Oracle Text  or some external indexer     To get substring indexing with Oracle Text you will need a CONTEXT index  It s a bit involved as it s made for indexing large documents and text using a lot of smarts  If you have particular needs  such as substring searches in numbers and all words  including  the   an   a   spaces  etc    you need to create custom lexers to remove some of the smart stuff     If you insert a lot of data  Oracle Text will not make things faster  especially if you need the index to be updated within the transactions and not periodically

User · Answer

You can put your values in ODCIVARCHAR2LIST and then join it as a regular table    select tabl1   FROM tabl1 LEFT JOIN   select column value txt from table sys ODCIVARCHAR2LIST    val1     val2     val3       Vals ON tabl1 column LIKE Vals txt WHERE Vals txt IS NOT NULL

User · Answer

Just to add on  Lukas Eder answer   An improvement to avoid creating tables and inserting values   we could use select from dual and unpivot to achieve the same result  on the fly       with all likes as    select   from       select   val1   like 1    val2   like 2    val3   like 3    val4   as like 4    val5   as like 5 from dual      unpivot        united columns for subquery column in   LIKE 1    LIKE 2    LIKE 3    LIKE 4    LIKE 5            select   from tbl     where exists  select 1 from all likes where tbl my col like all likes united columns

User · Answer

What would be useful here would be a LIKE ANY predicate as is available in PostgreSQL SELECT    FROM tbl WHERE my col LIKE ANY  ARRAY   val1      val2      val3           Unfortunately  that syntax is not available in Oracle  You can expand the quantified comparison predicate using OR  however  SELECT    FROM tbl WHERE my col LIKE   val1   OR my col LIKE   val2   OR my col LIKE   val3         Or alternatively  create a semi join using an EXISTS predicate and an auxiliary array data structure  see this question for details   SELECT   FROM tbl t WHERE EXISTS     SELECT 1   -- Alternatively  store those values in a temp table    FROM TABLE  sys ora mining varchar2 nt   val1      val2      val3                WHERE t my col LIKE column value    For true full-text search  you might want to look at Oracle Text  http   www oracle com technetwork database enterprise-edition index-098492 html

User · Answer

You don t need a collection type as mentioned in https   stackoverflow com a 6074261 802058  Just use an subquery  SELECT   FROM tbl t WHERE EXISTS       SELECT 1     FROM           SELECT  val1   AS val FROM dual         UNION ALL         SELECT  val2   AS val FROM dual         --             -- or simply use an subquery here           WHERE t my col LIKE val

User · Answer

No  you cannot do this  The values in the IN clause must be exact matches  You could modify the select thusly   SELECT     FROM tbl  WHERE my col LIKE  val1      OR my col LIKE  val2      OR my col LIKE  val3         If the val1  val2  val3    are similar enough  you might be able to use regular expressions in the REGEXP LIKE operator

User · Answer

I prefer this  WHERE CASE WHEN my col LIKE   val1   THEN 1                WHEN my col LIKE   val2   THEN 1            WHEN my col LIKE   val3   THEN 1            ELSE 0            END   1   I m not saying it s optimal but it works and it s easily understood  Most of my queries are adhoc used once so performance is generally not an issue for me

User · Answer

select   from tbl  where exists  select 1 from all likes where all likes value   substr tbl my col 0  length tbl my col

User · Answer

Yes  you can use this query  Instead of  Specialist  and  Developer   type any strings you want separated by comma and change employees table with your table   SELECT   FROM employees em     WHERE EXISTS  select 1 from table sys dbms debug vc2coll  Specialist    Developer    mt where em job like         mt column value             Why my query is better than the accepted answer  You don t need a CREATE TABLE permission to run it  This can be executed with just SELECT permissions

[sql] Using LIKE in an Oracle IN clause

Examples related to sql

Examples related to oracle

Examples related to sql-like