Usage of instr in oracle

The INSTR function is used in Oracle to find the position of the first match of a substring in a string, returning the position of the match (starting from 1), or 0 if there is no match. Other uses include: finding character positions, case-sensitive searches, and finding multiple matches.

Usage of INSTR function in Oracle

The INSTR function is used in Oracle database to find another string in a string. The position of the first occurrence of a string. The syntax is as follows:

<code>INSTR(string, substring)</code>

Among them:

• string: The string to be searched.
• substring: The substring to be found.

The INSTR function returns a number indicating the position of the substring in the string, starting from 1. If no match is found, 0 is returned.

Usage example:

<code>SELECT INSTR('Hello World', 'World') FROM dual;</code>

Output:

<code>7</code>

In this example, the INSTR function found the substring "World" in the string " Hello World" and returns 7, indicating that "World" starts at the 7th character of the string.

Other uses:

The INSTR function can also be used:

• Find the position of a specific character: To To find characters, just pass a single character as a substring. For example:

<code>SELECT INSTR('Hello World', 'l') FROM dual;</code>

• Case-sensitive search: By default, the INSTR function is not case-sensitive. To perform a case-sensitive search, use the INSTRB function.
• Find multiple matches: To find all matches, you can use INSTR combined with a subquery. For example:

<code>SELECT * FROM table WHERE INSTR(name, 'John') > 0;</code>

Note:

• The INSTR function is case-sensitive.
• If the substring is empty, the INSTR function returns 0.
• If the string is empty, the INSTR function returns NULL.

The above is the detailed content of Usage of instr in oracle. For more information, please follow other related articles on the PHP Chinese website!