1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle PL/SQL INSTR and SUBSTR Functions

Discussion in 'SQL PL/SQL' started by SBH, Nov 22, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    INSTR and SUBSTR are string functions which perform basic utilities functions on input string to return number and string output respectively. The article explains the usage of INSTR and SUBSTR functions.

    [​IMG]

    2. INSTR function

    INSTR function returns positional occurrence of a character or group of characters (substring) within an input string. By default, it returns the first occurrence of the character(s).

    Syntax

    Code (Text):
    INSTR (string, character[ or substring], position, occurrence)
    RETURN NUMBER
    • string: The string that is searched
    • character or substring: the substring to be searched for in the string
    • position: It is optional parameter which denotes the starting position of the search. It is set as 1 by default. It accepts both positive and negative values; where sign specifies the direction of search in the string i.e. forward or backward. If it is positive, search starts from the front of the string. If negative, the search starts from the back of the string.
    • occurrence It is optional parameter which denotes the occurrence count of character within the string. It is set as 1 by default.
    • Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype
    • If substring is not found within the string, the INSTR function returns 0.

    3. Examples

    Example 3.1. Below SQL shows the positional of L in the string 'CUBORACLE'

    Code (SQL):
    SELECT INSTR('CLUBORACLE','L') RES FROM DUAL;

    RES
    ---
    2

    Example 3.2. Below SQL shows the positional of L in the string 'CUBORACLE' after 3rd position

    Code (SQL):
    SELECT INSTR('CLUBORACLE','L',3) RES FROM DUAL;

    RES
    ---
    9
    Example 3.3. Below SQL shows the positional of L in the string 'CUBORACLE' after 3rd position from back of the string

    Code (SQL):
    SELECT INSTR('CUBORACLE' ,'L', -3) RES FROM DUAL;

    RES
    ---
    2
     
    4. Other forms of INSTR

    [​IMG]

    5. SUBSTR function

    SUBSTR function is used to pull substring from a large string.

    Syntax:

    Code (Text):
    SUBSTR(String, Start pos, End pos)
    RETURN STRING
    With input the String to take a substring from,
    Start pos and End pos are the optional parameters which specify the number of characters in the substring returned from the function.

    6. Examples

    Code (SQL):
    SELECT substr('CLUBORACLE',3,2) RES FROM dual;

    RES
    ----
    UB

    SELECT substr('CLUBORACLE',7) RES FROM dual;

    RES
    ----
    ACLE
     

    Attached Files: