Oracle中INSTR和SUBSTR的用法

Oracle中INSTR的用法：
INSTR方法的格式为
INSTR(源字符串, 要查找的字符串, 从第几个字符开始, 要找到第几个匹配的序号)
返回找到的位置，如果找不到则返回0.
例如：INSTR("CORPORATE FLOOR","OR", 3, 2)中，源字符串为"CORPORATE FLOOR", 在字符串中查找"OR"，从第三个字符位置开始查找"OR"，取第三个字后第2个匹配项的位置。

默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。

所以SELECT INSTR("CORPORATE FLOOR", "OR", -1, 1) "aaa" FROM DUAL的显示结果是

Instring
——————
14

oracle的substr函数的用法：
 取得字符串中指定起始位置和长度的字符串   substr( string, start_position, [ length ] )
 如:
     substr("This is a test", 6, 2)     would return "is"
     substr("This is a test", 6)     would return "is a test"
     substr("TechOnTheNet", -3, 3)     would return "Net"
     substr("TechOnTheNet", -6, 3)     would return "The"
 
select substr("Thisisatest", -4, 2) value from dual

综合应用：
SELECT INSTR("CORPORATE FLOOR", "OR", -1, 1) "Instring" FROM DUAL
--INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
SELECT INSTR("CORPORATE FLOOR","OR", 3, 2) "Instring" FROM DUAL

SELECT INSTR("32.8,63.5",",", 1, 1) "Instring" FROM DUAL

SELECT SUBSTR("32.8,63.5",INSTR("32.8,63.5",",", 1, 1)+1) "INSTRING" FROM DUAL
SELECT SUBSTR("32.8,63.5",1,INSTR("32.8,63.5",",", 1, 1)-1) "INSTRING" FROM DUAL

-- CREATED ON 2008-9-26 BY ADMINISTRATOR
DECLARE
  -- LOCAL VARIABLES HERE
  T   VARCHAR2(2000);
  S   VARCHAR2(2000);
  NUM INTEGER;
  I   INTEGER;
  POS INTEGER;
BEGIN
  -- TEST STATEMENTS HERE
  T := "12.3,23.0;45.6,54.2;32.8,63.5;";
  SELECT LENGTH(T) - LENGTH(REPLACE(T, ";", "")) INTO NUM FROM DUAL;
  DBMS_OUTPUT.PUT_LINE("NUM:" || NUM);
  POS := 0;
  FOR I IN 1 .. NUM LOOP
    DBMS_OUTPUT.PUT_LINE("I:" || I);
    DBMS_OUTPUT.PUT_LINE("POS:" || POS);
    DBMS_OUTPUT.PUT_LINE("==:" || INSTR(T, ";", 1, I));
    DBMS_OUTPUT.PUT_LINE("INSTR:" || SUBSTR(T, POS + 1, INSTR(T, ";", 1, I) - 1));
    POS := INSTR(T, ";", 1, I);
  END LOOP;
END;

-- Created on 2008-9-26 by ADMINISTRATOR
declare
  -- Local variables here
  i integer;
  T      VARCHAR2(2000);
  S      VARCHAR2(2000);
begin
  -- Test statements here
    --历史状态
  T := "12.3,23.0;45.6,54.2;32.8,63.5;";
  IF (T IS NOT NULL) AND (LENGTH(T) > 0) THEN
    --T := T || ",";
    WHILE LENGTH(T) > 0 LOOP
      --ISTATUSID := 0;
      S         := TRIM(SUBSTR(T, 1, INSTR(T, ";") - 1));
      IF LENGTH(S) > 0 THEN
         DBMS_OUTPUT.PUT_LINE("LAT:"||SUBSTR("32.8,63.5",1,INSTR("32.8,63.5",",", 1, 1)-1));
         DBMS_OUTPUT.PUT_LINE("LON:"||SUBSTR("32.8,63.5",INSTR("32.8,63.5",",", 1, 1)+1));
/*        INSERT INTO T_HISTORY_RESPONSESTATUS
          (HISTORYID, RESPONSESTATUSID, STAMP, CALL_LETTER)
        VALUES
          (IHISTORYID, S, ISTAMP, ICALL_LETTER);
*/        -- COMMIT;
      END IF;
      T := SUBSTR(T, INSTR(T, ";") + 1);
    END LOOP;
  END IF; 
end;