SQL 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

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.