SUBSTR And INSTR String Functions (SQL)

SUBSTR And INSTR String Functions (SQL)

SUBSTR (Substring) Built-in String Function 

SUBSTR (overload 1)
SUBSTR(
STR1 VARCHAR2 CHARACTER SET ANY_CS,
POS PLS_INTEGER, -- starting position
LEN PLS_INTEGER := 2147483647) -- number of characters
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

SUBSTR (overload 2)
SUBSTR(
STR1 CLOB CHARACTER SET ANY_CS,
POS NUMBER, -- starting position
LEN NUMBER := 2147483647) -- number of characters
RETURN CLOB CHARACTER SET STR1%CHARSET;

Substring Beginning Of String
SELECT SUBSTR(, 1, )
FROM dual;
SELECT SUBSTR('Take the first four characters', 1, 4) FIRST_FOUR
FROM dual;

Substring Middle Of String
SELECT SUBSTR(, , )
FROM dual.
SELECT SUBSTR('Take the first four characters', 16, 4) MIDDLE_FOUR
FROM dual;

Substring End of String
SELECT SUBSTR(, )
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(, , ,
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;