SQL INSTR Function

Create A Website SQL Data Warehousing CSS Database Normalization

SQL > SQL String Functions > INSTR Function

The INSTR function in SQL is used to find the starting location of a pattern in a string. This function is available in MySQL and Oracle, though they have slightly different syntaxes:

Syntax

The syntax for the INSTR function is as follows:

MySQL:

INSTR (str, pattern)

Find the staring location of pattern in string str.

Oracle:

INSTR (str, pattern, [starting position, [nth occurrence]])

Find the starting location of the nth occurrence of pattern beginning in the starting position-th position in string str.

Examples

We use the following table for our examples.

Table Geography

Region_Name Store_Name

East Boston

East New York

West Los Angeles

West San Diego

Example 1 (both Oracle and MySQL)

SELECT INSTR (Store_Name, 'o')
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

The first occurrence of 'o' is the second character in the word 'Los Angeles.'

Example 2 (both Oracle and MySQL)

SELECT INSTR (Store_Name, 'p')
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

In this case, the pattern p does not exist in string 'Los Angeles,' so the function returns 0.

Example 3 (Oracle only)

SELECT INSTR(Store_Name,'e', 1, 2)
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

In this case, we are looking for the second occurrence of the character 'e' in the word 'Los Angeles,' and we start the start with the first character of the word.

The function returns 10 as the second occurrence of 'e' is in the 10th position.

Next: SQL TRIM

This page was last updated on October 12, 2024.

SQL INSTR Function

Syntax

Examples

Example 1 (both Oracle and MySQL)

Example 2 (both Oracle and MySQL)

Example 3 (Oracle only)

AdBlock Detected!