Oracle SUBSTR & INSTR Functions
Version 11.2.0.3
 
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;
 
 
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