Oracle SUBSTR & INSTR Functions

点击打开链接

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;