INSTR

MySQL

INSTR(str,substr)

• str: This is the string that will be searched. It is the original string where you want to locate the presence of another substring.
• substr: This is the substring to be found within the str parameter. It is the targeted string that the user wants to identify inside the original string.

Example

SELECT INSTR('Hello World', 'World');

Output

6

Explanation

In the given example, INSTR('Hello World', 'World') returns the position of the first occurrence of substring ‘World’ in the string ‘Hello World’. The function counts from 1. Hence, the result is 6. If the substring was not found, it would return 0.

Oracle

INSTR(string, substring, [start], [nth])

• string: This is the source string where the search will be performed.
• substring: This is the sequence of characters to find within the source string.
• start: Optional parameter. It defines the position in the string where the search will start. By default it’s 1, i.e., the search begins at the first character of the source string.
• nth: Optional parameter. It specifies which occurrence of the substring to search. By default it’s 1, i.e., the function will find the first occurrence of the substring.

Example

SELECT INSTR('Hello World!', 'o') AS Position FROM dual;

Output

Position
--------
5

Explanation

The INSTR function in Oracle is used to find the location of a substring in a string. In the provided example, it’s searching for the first occurrence of ‘o’ in the string ‘Hello World!’, which is at the 5th position.

PostgreSQL

INSTR(string text, substring text) RETURNS integer

• string text: This is the string of text that SQL will search within. It is the larger body of text in which you expect to find the substring.
• substring text: This is the smaller string of text that SQL will try to locate within the larger string. If found, SQL will return its position as an integer value.
• returns integer: This indicates the output of the SQL INSTR function. It returns the location of the substring as a numeric value. If the substring is not found within the larger string, it will return zero.

Example

SELECT INSTR('PostgreSQL', 'SQL');

Output

5

Explanation

The INSTR function returns the position of the first occurrence of a substring in a string. In the example, it’s searching for the substring ‘SQL’ within the string ‘PostgreSQL’, which is found at position 5.

SQLite

INSTR(string, substring)

• string: This is the initial string or character expression in which the function will search for the “substring”.
• substring: This is the substring or small portion of the original string that the function will look for within the “string” parameter.

Example

SELECT INSTR('SQLite is cool', 'cool');

Output

9

Explanation

The INSTR function, in this example, checks for the index of the substring ‘cool’ within the string ‘SQLite is cool’. The position is 9, which means ‘cool’ starts at the 9th position in the string.