INSTR : CHAR VARCHAR2 Functions « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. CHAR VARCHAR2 Functions

INSTR locates one string/character in the other one:

v_nr:= instr(string,substring[,position,occurrence]);

INSTR returns the number of characters in the original string where the desired substring starts.

You can also specify the position from which you want the search to start (by default from the first character) and what occurrence of the desired string is required (by default, the first one).

SQL>
SQL> declare
  2      v1_tx VARCHAR2(20):='Hello, World!';
  3      v_nr  binary_integer;
  4  begin
  5      v_nr:= instr (v1_tx,'l');
  6      DBMS_OUTPUT.put_line(v_nr);
  7
  8      v_nr:= instr (v1_tx,'l',-2);
  9      DBMS_OUTPUT.put_line(v_nr);
 10
 11      v_nr:= instr (v1_tx,'l',2,2);
 12      DBMS_OUTPUT.put_line(v_nr);
 13  end;
 14  /
3
11
4

PL/SQL procedure successfully completed.

SQL>