today:
96
yesterday:
46
Total:
998,795

SQL INSTR

admin 2016.05.19 14:54 Views : 785

INSTR

Syntax

Description of instr.gif follows
Description of the illustration instr.gif

Purpose

The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

  • position is an nonzero integer indicating the character of string where Oracle Database begins the search. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

  • occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive.

Both string and substring can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The value returned is of NUMBER datatype.

Both position and occurrence must be of datatype NUMBER, or any datatype that can be implicitly converted to NUMBER, and must resolve to an integer. The default values of both position and occurrence are 1, meaning Oracle begins searching at the first character of string for the first occurrence of substring. The return value is relative to the beginning of string, regardless of the value of position, and is expressed in characters. If the search is unsuccessful (if substring does not appear occurrence times after the position character of string), then the return value is 0.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Examples

The following example searches the string CORPORATE FLOOR, beginning with the third character, for the string "OR". It returns the position in CORPORATE FLOOR at which the second occurrence of "OR" begins:

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
  "Instring" FROM DUAL;
 
  Instring
----------
        14

In the next example, Oracle counts backward from the last character to the third character from the end, which is the first O in FLOOR. Oracle then searches backward for the second occurrence of OR, and finds that this second occurrence begins with the second character in the search string :

SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
     FROM DUAL;
 
Reversed Instring
-----------------
               2

The next example assumes a double-byte database character set.

SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"
   FROM DUAL;

Instring in bytes
-----------------
               27
No. Subject Author Date Views
41 Monthly SUM admin 2023.05.22 36
40 LOOP TABLE UPDATE admin 2023.05.19 49
39 STARTUP admin 2022.09.01 83
38 ACTION admin 2019.01.11 6454
37 SELECT last_name FROM admin 2018.12.12 742
36 CREATE TABLE admin 2018.09.25 471
35 VERY IMPORTANT admin 2018.06.26 224
34 MISSIONARY_SUB admin 2018.06.25 198
33 TABLE PRINT admin 2018.06.18 185
32 GP PRINT admin 2018.05.31 917
31 How to convert DATE to NUMBER admin 2018.05.27 197
30 DATE Compare admin 2018.05.22 365
29 Oracle SQL Developer: Online Migration 3.1 admin 2018.04.25 119
28 How to Update Oracle XE admin 2018.04.25 137
27 SQL admin 2016.07.15 387
26 SUBSTR admin 2016.05.26 271
25 FOR LOOP admin 2016.05.20 268
24 ROWNUM admin 2016.05.19 240
23 NVL admin 2016.05.19 151
» INSTR admin 2016.05.19 785