CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle SUBSTR & INSTR Functions
Version 11.1
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;
Simplified Examples
Examples in Oracle/PLSQL of using the substr() function to extract a substring from a string:

The general syntax for the SUBSTR() 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 a positive number, then substr starts from the beginning of the string.

If the start_position is a negative number, then substr starts from the end 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.', 1, 6)    will return 'Dinner'
    substr('Dinner starts in one hour.', 0, 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.', -9, 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(
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, 1)+1)
FROM DUAL;
 
Related Topics
DBMS_LOB Built-in Package
Regular Expressions
String Functions
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [63 users online]    © 2010 psoug.org