Using Like in an Oracle in Clause

Using LIKE in an Oracle IN clause

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

Is there a combination of LIKE and IN in SQL?

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:

• Building Full-Text Search Applications with Oracle Text
• Understanding SQL Server Full-Text

How to use a variable in a LIKE clause in PL/SQL

Well, yes - those "translators" don't always do what they are supposed to.

This is how your code should look like:

• use like, not = in the where clause
• in PL/SQL, you have to put the result of the select statement into something - for example, locally declared variables (as my example shows).

So:

DECLARE
   v_SearchObj    VARCHAR2 (100) := '%aldbrough%';
   --
   v_obj_id       t_object.obj_id%TYPE;
   v_name         t_object.name%TYPE;
   v_description  t_object.description%TYPE;
BEGIN
   SELECT obj_id, NAME, DESCRIPTION
     INTO v_obj_id, v_name, v_description
     FROM agnis.t_object
    WHERE LOWER (NAME) LIKE v_searchobj;
END;

---

If such a code returns an error - too_many_rows (and yes, it does), then one option is to loop through rows and do something (such as display those values):

DECLARE
   v_SearchObj  VARCHAR2 (100) := '%aldbrough%';
BEGIN
   FOR cur_r IN (SELECT obj_id, NAME, DESCRIPTION
                   FROM agnis.t_object
                  WHERE LOWER (NAME) LIKE v_searchobj)
   LOOP
      DBMS_OUTPUT.put_line (
         'Name = ' || cur_r.name || ', description = ' || cur_r.description);
   END LOOP;
END;

Can the IN operator use LIKE-wildcards (%) in Oracle?

Select * from myTable m
where m.status not like 'Done%' 
and m.status not like 'Finished except%'
and m.status not like 'In Progress%'

Using LIKE and IN in Oracle SQL

It is not possible in Oracle to use LIKE and IN together.

You must have to write Multiple LIKE and use OR as follows:

select * from product 
where (product_code LIKE 'MS%' 
   OR product_code LIKE 'TS%');

Oracle SQL using Like and wildcard

You need to use ESCAPE clause:

You can include the actual characters % or _ in the pattern by using the ESCAPE clause, which identifies the escape character. If the escape character precedes the character % or _ in the pattern, then Oracle interprets this character literally in the pattern rather than as a special pattern-matching character. You can also search for the escape character itself by repeating it.

SELECT table_name
FROM user_tables
WHERE table_name LIKE 'APP!_X!_%' ESCAPE '!';

DBFiddle Demo

_ is treated as wildcard (any single character). But you need _ as literal.

How can I use `USING` clause with `LIKE` operator in PL/SQL?

That should be

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
  ' WHERE ' || t.column_name || ' like ''%'' || :1 || ''%'''
  INTO match_count
  USING 'NONE';

But with 'NONE' being a constant, why not simply:

EXECUTE IMMEDIATE
  'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
  ' WHERE ' || t.column_name || ' like ''%NONE%'''
  INTO match_count;

Case in where clause with column Like condition

try this:

select * from cms_tab 
where 
param_val is null
 or col1 like '%'||param_val||'%' 

Oracle SQL Like not working for hyphenated words

A method using like is:

WHERE description LIKE '%post%doctorate%'

But that is much more general than you want. So, use regular expressions:

WHERE REGEXP_LIKE(description, 'post[- ]?doctorate'

Or, if you want to allow any character to appear at most once:

WHERE REGEXP_LIKE(description, 'post(.)?doctorate'

---

Related Topics