SQL INSTR Function

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 June 19, 2023.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.