Oracle INSTR Function – SQL Syntax Examples

AshokTechnical Tips8 Comments

The Oracle INSTR SQL function is popular and performs materially the same operation as instr functions in many other programming languages.

Below shows the INSTR function along with the arguments it takes:

1
instr(string, SUBSTRING)
1
instr(string, SUBSTRING, start_position)
1
instr(string, SUBSTRING, start_position, occurrence)

The Oracle INSTR function returns the position (an integer) within string of the first character in substring that was found while using the corresponding start_position and occurrence.

Following are important rules to follow along with syntax exemplifying the implications of the rules.

  1. The first character of string is at start_position 1. start_position is defaulted as 1. If start_position is set to 0, 0 will always be returned, and thus, is not a useful value. If start_position is negative, searching for substring will begin at start_position characters counted from the end (right) of string and searching will be conducted towards the start (right-to-left) of string.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    SELECT instr('abcdabcd','b') FROM dual
    --returns: 2
     
    SELECT instr('abcdabcd','b',1) FROM dual
    --returns: 2
     
    SELECT instr('abcdabcd','b',4) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',0) FROM dual
    --returns: 0
     
    SELECT instr('abcdabcd','b',-1) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',-4) FROM dual
    --returns: 2
  2. substring may exist within string more than once. The ocurrence attribute is a positive integer that allows you specify which occurrence you are searching for. Remember, if you would like to find the 2cnd-to-last occurrence of substring within string, you should search using a start_position of -1 to indicate that searching should be conducted backwards, leaving occurrence at a value of 2.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    SELECT instr('abcdabcd','b',1,1) FROM dual
    --returns: 2
     
    SELECT instr('abcdabcd','b',1,2) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',1,0) FROM dual
    --returns: ORA-01428: argument '0' is out of range
     
    SELECT instr('abcdabcd','b',1,-1) FROM dual
    --returns: ORA-01428: argument '-1' is out of range
     
    SELECT instr('abcdabcd','b',-1,1) FROM dual
    --returns: 6
     
    SELECT instr('abcdabcd','b',-1,2) FROM dual
    --returns: 2

string and substringcan be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both start_position and ocurrence must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer (remember that start_position should not be 0 and ocurrence should be greater than or equal to 1). The return value is a NUMBER, and will return 0 when nothing is found in the search.

INSTR is most powerful and often used in practice with the Oracle SUBSTR SQL function. Here are some examples of how to combine SUBSTR with INSTR.

8 Comments on “Oracle INSTR Function – SQL Syntax Examples”

  1. Hello Team,

    I need some more explaination on how the below syntax is working

    “SELECT instr(‘abcdabcd’,’b’,1,1) FROM dual”

    Thanks & regards,

    Souvik Ghosh

    1. this syntax finds the first occurrence of “b” starting front he first character on the left, hence it will return the value 2. The syntax instr(‘abcdabcd’,’b’,1,2) finds the second occurrence and would return the value 6. you can also start from the right and search left. so instr(‘abcdabcd’,’a’,-1,1) would return 5, instr(‘abcdabcd’,’a’,-1,2) would return 1.

  2. Thanks for this. Got me headed in the right direction. I needed to parse out the second to last level in a URL where the “root” could vary (parsing Sharepoint folders). So, here is what I came up with:

    substr(doc_url,instr(document_url,’/’,-1,2)+1,instr(doc_url,’/’,-1,1)-instr(doc_url,’/’,-1,2)-1)

    returns hello for
    http://a.com/b/hello/world.doc and for
    http://a.com/hello/world.doc

  3. hi!
    i need help on nvarchar2, i tried storing values in different languages using nvarchar2;but once i retrieved them, they were in unspecified form….please help me out here…..

    thank you!

  4. SELECT instr(‘abcdabcd’,’b’,-1,1)

    I did not understand how the result is 6 of this code.Please can anyone tell me about this?

    1. The negative one (-1) indicates that the search will start on the right to left and find the first “b”. That position of “b” happens to be sixth from the left of the overall string. Hope this helps!

Leave a Reply

Your email address will not be published. Required fields are marked *