| 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; |
|
