Oracle SUBSTR & INSTR Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose The substring and instring functions are combined on this page because they are, quite often, used together. SUBSTRing extracts a string from a string and INSTRing is commonly used to determine the starting and/or ending points for the substringing operation by returning the position of an occurrence of a specific character.
 
SUBSTR (Substring) Built-in String Function
SUBSTR (overload 1) SUBSTR(
STR1 IN VARCHAR2 CHARACTER SET ANY_CS,
POS  IN PLS_INTEGER,                -- starting position
LEN  IN PLS_INTEGER := 2147483647)  -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;
SUBSTR (overload 2) SUBSTR(
str1 IN CLOB CHARACTER SET ANY_CS,
pos  IN INTEGER,
len  IN INTEGER := 18446744073709551615)
RETURN CLOB CHARACTER SET STR1%CHARSET;
Substring Beginning Of String SELECT SUBSTR(<value>, 1, <number_of_characters>)
FROM dual;
SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR
FROM dual;
Substring Middle Of String SELECT SUBSTR(<value>, <starting_position>, <number_of_characters>)
FROM dual;
SELECT SUBSTR('Take the first four characters', 16, 4) MIDDLE_FOUR
FROM dual;
Substring End of String SELECT SUBSTR(<value>, <starting_position>)
FROM dual;
SELECT SUBSTR('Take the first four characters', 16) SIXTEEN_TO_END
FROM dual;

SELECT SUBSTR('Take the first four characters', -4) FINAL_FOUR
FROM dual;
 
INSTR (Instring) Built-in String Function
INSTR (overload 1) INSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS,        -- test string
STR2 VARCHAR2 CHARACTER SET STR1%CHARSET,  -- string to locate
POS  PLS_INTEGER := 1,                     -- position
NTH  POSITIVE := 1)                        -- occurrence number
RETURN PLS_INTEGER;
INSTR (overload 2) INSTR(
STR1 CLOB CHARACTER SET ANY_CS,            -- test string
STR2 CLOB CHARACTER SET STR1%CHARSET,      -- string to locate
POS  INTEGER := 1,                         -- position
NTH  POSITIVE := 1)                        -- occurrence number
RETURN INTEGER;
Instring For Matching First Value Found SELECT INSTR(<value>, <value_to_match>, <direction>, <instance>
FROM dual;
SELECT INSTR('Take the first four characters', 'a', 1, 1) FOUND_1
FROM dual;
Instring If No Matching Second Value Found SELECT INSTR('Take the first four characters', 'a', 1, 2) FOUND_2
FROM dual;
Instring For Multiple Characters SELECT INSTR('Take the first four characters', 'four', 1, 1) MCHARS
FROM dual;
Reverse Direction Search SELECT INSTR('Take the first four characters', 'a', -1, 1) REV_SRCH
FROM dual;
Reverse Direction Search Second Match SELECT INSTR('Take the first four characters', 'a', -1, 2) REV_TWO
FROM dual;
 
String Parsing By Combining SUBSTR And INSTR Built-in String Functions
List parsing first value

Take up to the character before the first comma
SELECT SUBSTR('abc,def,ghi', 1, INSTR('abc,def,ghi', ',', 1, 1)-1)
FROM dual;
List parsing center value

Take the value between the commas
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 1)+1,
INSTR('abc,def,ghi',',',1,2)-INSTR('abc,def,ghi',',',1,1)-1)
FROM dual;
List parsing last value

Take the value after the last comma
SELECT SUBSTR('abc,def,ghi', INSTR('abc,def,ghi',',', 1, 2)+1)
FROM dual;

Related Topics
DBMS_LOB Built-in Package
REGEXP_SUBSTR
String Functions

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved