SQL INSTR Function

The INSTR string 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:

The syntax for the Length function is as follows:

MySQL: INSTR (str, pattern): Find the staring location of pattern in string str.

Oracle: INSTR (str, pattern, [starting position, [nth location]]): Find the staring location of the nth occurrence of pattern beginning in the starting position-th position in string str.

Let's take a look at some examples. Assume we have the following table:

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:

3

Example 2 (both Oracle and MySQL):

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

Result:

0

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:

10

The second occurrence of e is in the 10th position.