Oracle PL/SQL Programming, Third Edition

Name

INSTR, INSTRB, INSTRC, INSTR2, and INSTR4

Synopsis

The INSTR family of functions allow you to search a string to find a match for a substring. If the substring is found, the functions return the position, in the source string, of the first character of the substring. If there is no match, then the functions return 0.

The five INSTR functions differ only in terms of how they look at the string and substring:

INSTR

Strings consist of characters. The return value indicates the character position at which the substring is found.

INSTRB

Strings consist ofbytes. The return value indicates the byte position at which the substring is found.

INSTRC

Strings consist of Unicode characters. Decomposed Unicode characters are recognized (e.g., a\0303 is recognized as being the same as \00E3 or ã).

INSTR2

Looks at strings in terms of Unicode code units.

INSTR4

Looks at strings in terms of Unicode code points.

All of the INSTR functions share the same specification:

FUNCTION INSTR 
   (string1 IN VARCHAR2, 
    string2 IN VARCHAR2
   [,start_position IN NUMBER := 1 
   [, nth_appearance IN NUMBER := 1]])
RETURN NUMBER

where string1 is the string searched by INSTR for the position in which the nth_appearance of string2 is found. The start_position parameter is the character (not byte) position in the string where the search will start. It is optional and defaults to 1 (the beginning of string1). The nth_appearance parameter is also optional and defaults to 1.

Both the start_position and the nth_appearance ...