Oracle INSTR function

Last update on July 12 2024 11:20:52 (UTC/GMT +8 hours)

Description

The Oracle INSTR function is used to search for a substring within a string and return the position of the substring. It is commonly utilized in SQL queries to locate specific text fragments, analyze text data, or validate strings. The function returns an integer indicating the position of the first character of the substring found. If no such substring is found, it returns zero.

Usage Context:

The INSTR function is particularly useful in scenarios where precise substring location is critical, such as text analysis, data parsing, or validation checks in SQL queries.

Real-world Examples:

For instance, in a database of product descriptions, INSTR can help identify and extract keywords or tags for indexing and search optimization.

Syntax:

{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])

INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

Parameters:

Name	Description	Data Types
string:	The target string where the search is performed. Can be any data type that supports text.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB, exceptions are INSTRC, INSTR2, and INSTR4, which do not allow string to be a CLOB or NCLOB.
substring:	The text fragment you're searching for within the target string.	CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.
position:	Optional. The position is an nonzero integer indicating the character of string from where the search begins. If omitted, it defaults to 1. The first position in the string is 1. If position is negative, then INSTR function counts backward from the end of string and then searches backward from the resulting position.	NUMBER, or any data type that can be implicitly converted to NUMBER and must resolve to an integer.
occurrence	Optional, The occurrence is an integer indicating which occurrence of substring in string INSTR function should search for. The default value is 1, meaning the first occurrence.	NUMBER, or any data type that can be implicitly converted to NUMBER and must resolve to an integer.

Return Value:

Returns the position of the first character of the found substring or zero if not found.

Return Value Type

NUMBER

Applies to

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Examples: Oracle INSTR function

The following examples explore how the INSTR function works.

Basic Usage:


-- The following SQL statement uses the INSTR function to find the position 
--of the substring 'TH' within the string 'THIS IS THE THING'.
SELECT 
    INSTR('THIS IS THE THING', -- The main string to search within.
          'TH'                -- The substring to find within the main string.
         ) "Position Found"    -- Aliases the result column as "Position Found" for readability.
FROM 
    DUAL;                      -- The DUAL table is a special dummy table used in 
	                           --Oracle to perform calculations or retrieve constants.

Explanation:

The query returns the position of the first occurrence of the substring 'TH' within the target string.

Positions in Oracle strings are 1-based, meaning the first character is at position 1.

The result of this query will be an integer representing the position of the substring 'TH' within the string 'THIS IS THE THING'.

Since 'TH' appears at the very beginning, the expected output is 1.

Sample Output:

Position Found
--------------
             1

Empty Substring:


-- The following SQL query uses the INSTR function to locate the position of the first space character (' ') in the string 'THIS IS THE THING'.
SELECT 
    INSTR('THIS IS THE THING', -- The string in which we are searching for the space character.
          ' '                 -- The substring to search for, which in this case is a single space character.
         ) "Position Found"   -- Aliases the result column as "Position Found" for clarity in the output.
FROM 
    DUAL;                     -- Uses the DUAL table, a special system table in Oracle, to execute this query.

Returns 1 because an empty substring is considered to be found at the first position

Explanation:

This SQL query is designed to find the position of the first space character ' ' in the string 'THIS IS THE THING'.

The INSTR function is utilized here, which searches for the specified substring (in this case, a space) within the target string.

Target String: 'THIS IS THE THING' is the string we are analyzing.

Substring to Find: ' ' is the space character we are looking for within the target string.

Result: The function returns the position of the first occurrence of the space character within the string. Since the first space in the string 'THIS IS THE THING' appears after the word 'THIS', the expected output is 5.

The query's result will be displayed under the column header "Position Found", indicating the position of the first space character in the target string.

In this context, the DUAL table is used to execute the query, as it serves as a dummy table that allows us to perform operations without referencing any actual data table.

Position Beyond String Length:


-- The following SQL query uses the INSTR function to find the position of the substring 'Oracle' within the string 'Oracle Database'.
-- The search starts from the 20th character in the string.
SELECT 
    INSTR('Oracle Database',  -- The string in which we are searching for the substring 'Oracle'.
          'Oracle',           -- The substring to search for within the target string.
          20                  -- The position from which the search begins. Since 20 is greater than the length of the string, no match will be found.
         ) "Position Found"   -- Aliases the result column as "Position Found" for clarity in the output.
FROM 
    DUAL;                     -- Uses the DUAL table, a special system table in Oracle, to execute this query.

Explanation:

This SQL query is designed to search for the substring 'Oracle' within the string 'Oracle Database'.

The INSTR function is used here to locate the position of the specified substring.

The function is instructed to begin the search from the 20th character of the target string.

Target String: 'Oracle Database' is the string where the search is conducted.

Substring to Find: 'Oracle' is the substring we are looking for within the target string.

Starting Position: The search starts at the 20th character of the target string.

Since the string 'Oracle Database' has fewer than 20 characters (its length is 15), the starting position is beyond the length of the string.

Therefore, the INSTR function will not find the substring 'Oracle' and will return 0, indicating that no match was found.

The result of this query will be shown under the column header "Position Found", reflecting the position where the substring starts or 0 if the substring is not found, as in this case.

The DUAL table is used here as a placeholder to allow the query to be executed without referencing any actual data table.

Within String Length:


-- This SQL query uses the INSTR function to find the position of the substring 'TH' within the string 'THIS IS THE THING'.
-- The search begins at the 1st character of the target string.
-- The search looks for the 1st occurrence of the substring 'TH'.
SELECT 
    INSTR('THIS IS THE THING',  -- The string in which we are searching for the substring 'TH'.
          'TH',                 -- The substring to find within the target string.
          1,                    -- The position to start the search from (1st character).
          1                     -- The occurrence of the substring to find (1st occurrence).
         ) "Position Found"     -- Aliases the result column as "Position Found" for clarity in the output.
FROM 
    DUAL;                       -- Uses the DUAL table, a special system table in Oracle, to execute this query.

Explanation:

This SQL query searches for the first occurrence of the substring 'TH' within the string 'THIS IS THE THING' using the INSTR function.

The function searches for the substring 'TH' starting from the first character of the target string and identifies the position of its first occurrence.

The result of the INSTR function in this context will be 1, as the substring 'TH' appears at the very beginning of the target string 'THIS IS THE THING'.

The query uses the DUAL table, which is a special one-row, one-column table used in Oracle to execute queries that do not require data from an actual table.

Sample Output:

Position Found
--------------
             1

Specifying Position and Occurrence:


SELECT 
    INSTR('THIS IS THE THING',  -- The string to be searched.
          'TH',                 -- The substring to find within the main string.
          1,                    -- The starting position for the search (position 1, meaning the search starts at the beginning of the string).
          2                     -- The occurrence to find (the second occurrence of 'TH' in the string).
         ) "Position Found"     -- Aliases the result column as "Position Found" for clarity in the output.
FROM 
    DUAL;                       -- Uses the DUAL table, a special system table in Oracle, to execute this query.

Explanation:

This SQL query uses the INSTR function to find the position of a specific occurrence of a substring within a string.

Parameters for the INSTR function are as follows:

'THIS IS THE THING': The main string in which the search is performed.

'TH': The substring to search for within the main string.

1: The position in the main string to start the search (starting from the first character).

2: Specifies that the function should find the position of the second occurrence of the substring 'TH'.

The query selects the position of the second occurrence of 'TH' in the main string and labels the output as "Position Found".

Sample Output:

Position Found
--------------
             9

Using Negative Position:



SELECT 
    INSTR('THIS IS THE THING',  -- The string in which to search for the substring 'TH'.
          'TH',                 -- The substring to find within the target string.
          -3,                   -- The position to start the search from, counted backwards from the end of the string.
          3                     -- The occurrence of the substring to find (third occurrence).
         ) "Position Found"     -- Aliases the result column as "Position Found" for clarity in the output.
FROM 
    DUAL;                       -- Uses the DUAL table, a special system table in Oracle, to execute this query.

Explanation:

This SQL query uses the INSTR function to find the position of the third occurrence of the substring 'TH' within the string 'THIS IS THE THING'.

The search starts from the third character from the end of the string due to the negative starting position.

The parameters are set to:

- 'THIS IS THE THING': The string to search in.

- 'TH': The substring to search for within the main string.

- -3: The starting position for the search, counted backwards from the end of the string.

- 3: The function is searching for the third occurrence of the substring 'TH'.

Since the string 'THIS IS THE THING' contains 'TH' at positions 1, 9, and 13 when counted forward, the search will consider these occurrences. The position of the third occurrence, starting the search backward from the position before 'NG', is at position 1 in the string.

Sample Output:

Position Found
--------------
             1

Advanced Usage:

Combining with SUBSTR:


SELECT 
    SUBSTR('Oracle Database',   -- The original string from which a portion will be extracted.
           INSTR('Oracle Database', 'Database'),  -- The starting position of the substring to extract, found by INSTR.
           8                    -- The number of characters to extract, starting from the INSTR position.
          ) "Extracted Text"    -- Aliases the result column as "Extracted Text" for clarity in the output.
FROM 
    DUAL;                       -- Uses the DUAL table, a special system table in Oracle, to execute this query.

Explanation:

This SQL query extracts a substring from the string 'Oracle Database'.

It uses the SUBSTR function to specify which part of the string to extract.

The parameters for the SUBSTR function are:

'Oracle Database': The original string from which the substring will be extracted.

INSTR('Oracle Database', 'Database'): The starting position for the substring, calculated by the INSTR function.

INSTR('Oracle Database', 'Database') finds the starting position of the substring 'Database' within 'Oracle Database'.

In this case, 'Database' starts at the 8th position in the string 'Oracle Database'.

8: The length of the substring to extract, starting from the position found by the INSTR function.

This extracts 8 characters starting from the position found by INSTR, which is position 8.

The query selects this extracted substring and labels the output as "Extracted Text".

Sample Output:

Extracted Text
--------------
Database

Comparisons with Similar Functions:

Function	Description	Example Usage
INSTR	Finds the position of a substring	INSTR('Hello World', 'World')
SUBSTR	Extracts a substring from a string	SUBSTR('Hello World', 7, 5)
REGEXP_INSTR	Finds the position using regular expressions	REGEXP_INSTR('Hello World', '\bWorld\b')

Frequently Asked Questions (FAQ) - Oracle INSTR Function

1. What is the Oracle INSTR Function?

The Oracle INSTR function is a SQL function used to search for a specific substring within a given string and return the position where this substring first appears. If the substring is not found, the function returns zero.

2. When should we use the Oracle INSTR function?

We should use the INSTR function when we need to determine the position of a substring within a string. This is useful for text analysis, parsing, or validating strings in SQL queries. It's particularly valuable in situations where locating precise text fragments is essential.

3. How does the Oracle INSTR function handle different data types?

The INSTR function can handle various data types including CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, and NCLOB for both the string to be searched and the substring. However, certain variations like INSTRC, INSTR2, and INSTR4 do not support CLOB or NCLOB as input.

4. What are some practical applications of the Oracle INSTR function?

The INSTR function is commonly used to:

Extract keywords or tags from text data for indexing and search optimization.

Validate and ensure the presence of specific text patterns within strings.

Analyze and manipulate text data in databases, such as identifying specific segments within product descriptions or customer feedback.

Can the INSTR function search backwards in a string?

Yes, the INSTR function can search backwards in a string if the position parameter is negative. This means it starts counting from the end of the string and searches backward from that position.

5. What does the Oracle INSTR function return?

The INSTR function returns an integer representing the position of the first character of the found substring within the main string. If the substring is not found, it returns zero.

6. How does the Oracle INSTR function differ in handling occurrences?

The INSTR function allows you to specify which occurrence of the substring to find. By default, it searches for the first occurrence, but you can specify any positive integer to find the nth occurrence of the substring.

7. Which Oracle versions support the Oracle INSTR function?

The INSTR function is supported in Oracle versions including Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, and Oracle 8i.

8. How does the Oracle INSTR function integrate with other Oracle SQL functions?

The INSTR function can be combined with other SQL functions like SUBSTR to perform more complex text manipulations. For example, you can use INSTR to locate the position of a substring and then use SUBSTR to extract or manipulate text starting from that position.

9. Are there different variations of the Oracle INSTR function?

Yes, there are variations such as INSTRB, INSTRC, INSTR2, and INSTR4. These variations are designed to work with different character encodings or byte-level searches. For instance, INSTRB operates at the byte level rather than the character level.

Previous: ASCII
Next: LENGTH