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:

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_NameStore_Name
EastBoston
EastNew York
WestLos Angeles
WestSan Diego

Example 1 (both Oracle and MySQL)

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

Result:

2

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.

Next: SQL TRIM




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact