Oracle中的INSTR,NVL和SUBSTR函数的用法详解

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

     -- COMMIT;

    END IF;

    T := SUBSTR(T, INSTR(T, ';' ) + 1);

   END LOOP;

  END IF;

end ;

SELECT INSTR( 'CORPORATE FLOOR' , 'OR' , 0, 1) FROM DUAL; 返回值为0

  SELECT INSTR( 'CORPORATE FLOOR' , 'OR' , 2, 1) FROM DUAL; 返回值为2

  SELECT INSTR( 'CORPORATE FLOOR' , 'OR' , 2, 2) FROM DUAL; 返回值为5

  SELECT INSTR( 'CORPORATE FLOOR' , 'OR' , -1, 1) FROM DUAL; 返回值为14

  SELECT INSTR( 'CORPORATE FLOOR' , 'OR' , -5, 1) FROM DUAL; 返回值为5

SELECT SUBSTR( 'This is a test' , 0, 2) value from dual; 返回值Th

  SELECT SUBSTR( 'This is a test' , 1, 2) value from dual; 返回值Hi

  SELECT SUBSTR( 'This is a test' , -1, 2) value from dual; 返回值t

  SELECT SUBSTR( 'This is a test' , -2, 2) value from dual; 返回值st

SELECT nvl( 'pos1' , null ) from dual; 返回值为pos1

  SELECT nvl( null , 'pos2' ) from dual; 返回值为pos1

  SELECT nvl( null , null ) from dual;  返回值为 null