How to use Like Operator with VARIABLE (ORACLE)

Question

Can you please tell me how can | pass variable in an oracle LIKE statement?

here is the plsql :

declare
variables VARCHAR2 (200):= 'exemple';
V1 integer ;

BEGIN
select COUNT(*)into V1 from user_source 
where name LIKE '%'+variables+'%'
;

      DBMS_OUTPUT.PUT_LINE(V1);
  
END;

PS : i need to use the % in the like sql statement. it's not just : where name LIKE variables the % operator is necessary ! thank you for the help !

Answer 1

Like this:

DECLARE
variables VARCHAR2 (200):= 'exemple';
V1 integer;

BEGIN
select COUNT(*)
  into V1 
  from user_source 
 where name like '%'||variables||'%';

 DBMS_OUTPUT.PUT_LINE(V1);
  
END;

Answer 2

Concatenate, or use INSTR function:

SQL> set serveroutput on
SQL> DECLARE
  2     variables  VARCHAR2 (200) := 'TEST';
  3     v1         INTEGER;
  4  BEGIN
  5     SELECT COUNT (*)
  6       INTO v1
  7       FROM user_source
  8      WHERE UPPER (name) LIKE '%' || UPPER (variables) || '%';
  9
 10     DBMS_OUTPUT.put_line (v1);
 11
 12     --
 13
 14     SELECT COUNT (*)
 15       INTO v1
 16       FROM user_source
 17      WHERE INSTR (UPPER (name), UPPER (variables)) > 0;
 18
 19     DBMS_OUTPUT.put_line (v1);
 20  END;
 21  /
90
90

PL/SQL procedure successfully completed.

SQL>