## Syntax
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
Start((START)) --> REGEXP_INSTR[REGEXP_INSTR]:::quoted
REGEXP_INSTR --> End((END))
```
## Purpose
The `REGEXP_INSTR` SQL function determines the position of the regular expression in the given value. Returns 0 when the regular expression is not contained in the given value. The regular expression must follow the Microsoft.net regular expression language.
See also [[REGEXP_REPLACE]] and [[REGEXP_SUBSTR]].
Parameters:
- Input (`varchar2`): The text to get the regular expression position from.
- Pattern (`varchar2`): Regular expression pattern.
- Start position (optional, `int32`): The start index from the input. Defaults to `1`.
- Appearance (optional, `int32`): Indicating the appearance of the instr operation. Defaults to `1`.
- ReturnOption (optional, `int32`): Select either the first character found (`0`) or the first character after the occurrence of the pattern (`1`). Defaults to `0`.
- Match_parameter (optional, `varchar2`): A text literal that lets you change the default matching behaviour of the function. Defaults to `''`.
Returns: the location of a regular expression pattern in the input.
## Examples
The following example retrieves the position of the first lower case `i` in `Invantive`:
```sql
select regexp_instr('Invantive', 'i')
-------------------
7
```
The following example retrieves the position of the first character after `i` in `Invantive`, ignoring case:
```sql
select regexp_instr('Invantive', 'i', 1, 1, 1, 'i')
-------------------
2
```
The following example retrieves 0 since the pattern does not occur in the input:
```sql
select regexp_instr('Invantive', 'h')
-------------------
0
```