Oracle PL/SQL - PL/SQL INSTR function

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. CHAR and VARCHAR2 Variables Size

Introduction

The function INSTR locates one string/character in the other one.

You can declare it as shown here: 

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

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

You can specify the position from which the search to start.

By default from the first character.

You can set what occurrence of the desired string is required, default to the first one.

Demo

SQL>
SQL> declare--  www . j ava 2 s.c o  m
  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      v_nr:= instr (v1_tx,'l',-2);
  8      DBMS_OUTPUT.put_line(v_nr);
  9      v_nr:= instr (v1_tx,'l',2,2);
 10      DBMS_OUTPUT.put_line(v_nr);
 11  end;
 12  /
3
11
4

PL/SQL procedure successfully completed.

SQL>

Related Topic