SUBSTR (Substring) Built-in String Function
SUBSTR(overload 1)
SUBSTR(
STR1VARCHAR2CHARACTER SET ANY_CS,
POSPLS_INTEGER,-- starting position
LENPLS_INTEGER:= 2147483647) -- number of characters
RETURNVARCHAR2CHARACTER SET STR1%CHARSET;
SUBSTR(overload 2)
SUBSTR(
STR1CLOBCHARACTER SET ANY_CS,
POSNUMBER,-- starting position
LENNUMBER:= 2147483647) -- number of characters
RETURNCLOBCHARACTER SET STR1%CHARSET;
Substring Beginning Of String
SELECTSUBSTR(<value>,1,<number_of_characters>)
FROMDUAL;
SELECTSUBSTR('Take the first four characters',1,4) FIRST_FOUR
FROMDUAL;
Substring Middle Of String
SELECTSUBSTR(<value>,<starting_position>,<number_of_characters>)
FROMDUAL.
SELECTSUBSTR('Take the first four characters',16,4) MIDDLE_FOUR
FROMDUAL;
Substring End of String
SELECTSUBSTR(<value>,<starting_position>)
FROMDUAL;
SELECTSUBSTR('Take the first four characters',16) SIXTEEN_TO_END
FROMDUAL;
SELECTSUBSTR('Take the first four characters',-4) FINAL_FOUR
FROMDUAL;
Simplified Examples
Examples in Oracle/PLSQL of using thesubstr()function to extract a substring from a string: The general syntax for theSUBSTR() function is:
SUBSTR( source_string,start_position,[ length ] )
"source_string" is the original source_string that the substring will be taken from.
"start_position" is the position in the source_string where you want to start extracting characters.The first position in the string is always '1',NOT '0',as in many other languages.
"length" is an optional parameter that specifies how many characters to extract. If this parameter is not used,SUBSTR will return everything from the start_position to the end of the string.
Notes:
If the start_position is specified as "0",substr treats start_position as "1",that is,as the first position in the string.
If the start_position is apositivenumber,then substr starts from thebeginningof the string.
If the start_position is anegativenumber,then substr starts from theend of the string and counts backwards.
If the length is a negative number,then substr will return a NULL value.
Examples:
substr('Dinner starts in one hour.',8,6) will return 'starts'
substr('Dinner starts in one hour.',8) will return 'starts in one hour.'
substr('Dinner starts in one hour.',6) will return 'Dinner'
substr('Dinner starts in one hour.',-4,3) will return 'our'
substr('Dinner starts in one hour.',-9,3) will return 'one'
substr('Dinner starts in one hour.',2) will return 'on'
This function works identically in Oracle 8i,Oracle 9i,Oracle 10g,and Oracle 11g.
INSTR (Instring) Built-in String Function
INSTR(overload 1)
INSTR(
STR1VARCHAR2CHARACTER SET ANY_CS,-- test string
STR2VARCHAR2CHARACTER SET STR1%CHARSET,-- string to locate
POSPLS_INTEGER:= 1,-- position
NTHPOSITIVE:= 1) -- occurrence number
RETURNPLS_INTEGER;
INSTR(overload 2)
INSTR(
STR1CLOBCHARACTER SET ANY_CS,-- test string
STR2CLOBCHARACTER SET STR1%CHARSET,-- string to locate
POSINTEGER:= 1,-- position
NTHPOSITIVE:= 1) -- occurrence number
RETURNINTEGER;
Instring For Matching First Value Found
SELECTINSTR(<value>,<value_to_match>,<direction>,<instance>
FROMDUAL;
SELECTINSTR('Take the first four characters','a',1) FOUND_1
FROMDUAL;
Instring If No Matching Second Value Found
SELECTINSTR('Take the first four characters','a',2) FOUND_2
FROMDUAL;
Instring For Multiple
Characters
SELECTINSTR('Take the first four characters','four',1) MCHARS
FROMDUAL;
Reverse Direction Search
SELECTINSTR('Take the first four characters',-1,1) REV_SRCH
FROMDUAL;
Reverse Direction Search Second Match
SELECTINSTR('Take the first four characters',2) REV_TWO
FROMDUAL;
String Parsing By Combining SUBSTR And INSTR Built-in String Functions
List parsing first value
Take up to the character before the first comma
SELECTSUBSTR('abc,def,ghi',1,INSTR('abc,',1)-1)
FROMDUAL;
List parsing center value
Take the value between the commas
SELECTSUBSTR('abc,1)+1,
INSTR('abc,2)-INSTR('abc,1)-1)
FROMDUAL;
List parsing last value
Take the value after the last comma
SELECTSUBSTR('abc,1)+1)
FROMDUAL;
