MariaDB INSTR() Function

In MariaDB, the built-in string INSTR() function returns the index of the first occurrence of a substring within a string.

MariaDB INSTR() is case insensitive. The function is identical to the function andtheINSTR() function with two parameters .LOCATE()POSITION()

MariaDB INSTR() Syntax

Here is the syntax of the MariaDB INSTR() function:

INSTR(str, substr)

Parameters

str

Required. The string to be searched for.

substr

Required. The substring to search for in str.

If you provide no parameters or use the wrong number of parameters, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'INSTR'.

Return value

The MariaDB INSTR(str, substr) function returns the numeric index of the position of the substring substr within the string str. Numeric indexes start at 1. If not found substr in str, the INSTR() function will return 0.

If substring is an empty string, the INSTR() function will return 1.

If either argument is NULL, the INSTR() function will return NULL.

MariaDB INSTR() Examples

Basic Example

The following statement shows the basic usage of the MariaDB INSTR() function:

SELECT
  INSTR('Hello World', 'He'),
  INSTR('Hello World', 'NO')\G

Output:

INSTR('Hello World', 'He'): 1
INSTR('Hello World', 'NO'): 0

Case insensitive

The MariaDB INSTR() function performs case-insensitive searches, the following statement says this:

SELECT
  INSTR('Hello World', 'He'),
  INSTR('Hello World', 'he'),
  INSTR('Hello World', 'HE')\G

Output:

INSTR('Hello World', 'He'): 1
INSTR('Hello World', 'he'): 1
INSTR('Hello World', 'HE'): 1

Empty string

If substring is an empty string, the INSTR() function will return 1.

SELECT INSTR('Hello World', '');

Output:

+--------------------------+
| INSTR('Hello World', '') |
+--------------------------+
|                        1 |
+--------------------------+

Conclusion

The MariaDB INSTR() function returns the index of the first occurrence of a substring within a string.