ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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(
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(
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;
