CODEWITHSUNDEEP
sqloracle-databasesql-like
DeveloperM
DeveloperM

Reputation: 1239

Using LIKE in an Oracle IN clause

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%',....)

Upvotes: 33

Views: 258708

Answers (11)

GabrielBB
GabrielBB

Reputation: 2679

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.

Upvotes: 8

Lukas Eder
Lukas Eder

Reputation: 220762

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

Upvotes: 54

Imran
Imran

Reputation: 169

In Oracle you can use regexp_like as follows:

select *
from   table_name
where  regexp_like (name, '^(value-1|value-2|value-3....)');

The caret (^) operator to indicate a beginning-of-line character & The pipe (|) operator to indicate OR operation.

Upvotes: 8

Toru
Toru

Reputation: 905

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
)

Upvotes: 0

Dickson katto
Dickson katto

Reputation: 21

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

Upvotes: 2

Jean-Philippe Martin
Jean-Philippe Martin

Reputation: 911

This one is pretty fast :

select * from listofvalue l 
inner join tbl on tbl.mycol like '%' || l.value || '%'

Upvotes: 4

AWOLKiwi
AWOLKiwi

Reputation: 29

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.

Upvotes: 2

PKey
PKey

Reputation: 3841

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)

Upvotes: 2

Vishnu
Vishnu

Reputation: 11

select * from tbl
 where exists (select 1 from all_likes where all_likes.value = substr(tbl.my_col,0, length(tbl.my_col)))

Upvotes: 1

KarlP
KarlP

Reputation: 5181

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.

Upvotes: 12

DCookie
DCookie

Reputation: 43523

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.

Upvotes: 11

Related Questions