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