Oracle instr function usage and (oracle uses instr to replace like), oracleinstr

Oracle instr function usage and (oracle uses instr to replace like), oracleinstr

Oracle instr function appeared in the recent project, so I found some information about it on the Internet.

Usage of INSTR and SUBSTR in Oracle

INSTR usage in Oracle:
The format of the INSTR method is
INSTR (source string, the string to be searched, starting from the nth character, to find the nth matching serial number)
Returns the location found. If not, 0 is returned.
For example, in INSTR ('upgrade FLOOR ', 'or', 3, 2), the source string is 'upgrade FLOOR', and the string is 'or ', search for "OR" from the third character, and take the location of the third character and the second matching item.

The default search order is left to right. When the start position is negative, search from the right.

Therefore, the result of select instr ('upgrade FLOOR ',' OR ',-1, 1) "aaa" FROMDUAL is:

Instring
------
14

Usage of the substr function in oracle:
 Returns the string with the specified start position and length. Substr (string, start_position, [length])
 For example:
    Substr ('this is a test', 6, 2)   Wocould return 'is'
    Substr ('this is a test', 6)   Wocould return 'is a Test'
    Substr ('techonthenet ',-3, 3)   Wocould return 'net'
    Substr ('techonthenet ',-6, 3)   Wocould return 'the'
 
Select substr ('thisatest ',-4, 2) value from dual

Comprehensive application:
Select instr ('ate FLOOR ',' OR ',-1, 1) "Instring" FROMDUAL
-- INSTR (source string, target string, start position, matching serial number)
Select instr ('ate 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 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 by ADMINISTRATOR
Declare
 -- Local variables here
 I integer;
 T    VARCHAR2 (2000 );
 S    VARCHAR2 (2000 );
Begin
 -- Test statements here
   -- Historical Status
 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 | ',';
   WHILELENGTH (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 );
   ENDLOOP;
 End if;
End;

 

Oracle instr Functions

For instr functions, we often use this method to find the position of the specified substring from a string. For example:

SQL> select instr ('oracle ',' or ') position from dual;

POSITION

----------

1

Start from the first position of the string 'oracle 'and look back for the first location where the substring 'or' appears.

In fact, there are four instr parameters in the format of "instr (string, substring, startposition, occurrence )". You can search for substrings as follows:

1. Search for substrings from the specified position

2. Specify the position of the substring that appears for the first time.

3. Search from the back and forward

-- 1. Search from 3rd characters

SQL> select instr ('oracleor', 'or', 3) position from dual;

POSITION

----------

7

-- 2. Search for the position where the substring appears for 1st times starting from 2nd characters

SQL> select instr ('oracleor', 'or', 1, 2) position from dual;

POSITION

----------

7

-- 3. Search for the position where the substring appears for 1st times starting from the last 1st characters

SQL> select instr ('oracleor', 'or',-1, 1) position from dual;

POSITION

----------

7

-- 3. Search for the position where the substring appears for 1st times starting from the last 2nd characters

SQL> select instr ('oracleor', 'or',-1, 2) position from dual;

POSITION

----------

1

Oracle uses instr instead of like

 

The table has nearly 1 million data records. In most cases, we need to perform string matching. in SQL statements, we usually use like to achieve our search goal. However, the actual test shows that the like efficiency is quite different from the instr function. The following are some test results:

SQL> set timing on
SQL> select count (*) from t where instr (title, 'oracle ')> 0;

COUNT (*)
----
5478

Elapsed: 00:00:11. 04
SQL> select count (*) from t where title like '% oracle % ';

COUNT (*)
----
5478

Elapsed: 00:00:31. 47
SQL> select count (*) from t where instr (title, 'oracle ') = 0;

COUNT (*)
----
994530

Elapsed: 00:00:11. 31
SQL> select count (*) from t where title not like '% oracle % ';

COUNT (*)
----
994530

Note:

Instr (title, 'oracle ')> 0 is equivalent to like

Instr (title, 'oracle ') = 0 is equivalent to not like