INSTR

Returns the position of the first character of patt (pattern) in expr relative to the beginning of expr.

Syntax

INSTR(expr, patt[, pos[, occ]])

Arguments

expr: The STRING value to be processed.

---

patt: The STRING value to search for.

---

pos (optional): Integer. The position in expr where the search starts. The default value is 1. If pos < 0, the search counts backwards from the end of expr and then searches backwards from the resulting position.

---

occ (optional): Integer. Specifies which occurrence of patt to return the position of the first character of patt.

Usage Instructions

• The return value is of the data type INTEGER.

• Regardless of the position value pos, the position is returned relative to the beginning of expr.

Examples

select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig') as result --1
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',10) as result --2
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',12) as result --3
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',10,2) as result --4
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',12,2) as result --5
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',-1,1) as result --6
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',-1,2) as result --7
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',1,1) as result --8
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'ig',1,2) as result --9
union all
select 
    instr('Gaudeamus igitur, Omnes homines dum sumus. Gaudeamus igitur, Iuvenes dum sumus.', 'Not here') as result; --10

+--------+
| RESULT |
+--------+
| 11     | --1
| 11     | --2
| 54     | --3
| 54     | --4
| 0      | --5
| 54     | --6  # compare with result 8 and 9
| 11     | --7  # compare with result 8 and 9
| 11     | --8  # compare with result 6 and 7
| 54     | --9  # compare with result 6 and 7
| 0      | --10
+--------+