SQL instr () and LOCATE () string lookup Functions

Because the subcategories of a website today use the SQL instr () and LOCATE () string operation functions, let's take a note and put it down. If you need them, please refer to them.

INSTR (str, substr)
Returns the first occurrence position of the str substring. This is the same as the double parameter form of LOCATE () unless the order of parameters is reversed.

The Code is as follows:	Copy code
Mysql> select instr ('foobar', 'bar '); -> 4 Mysql> select instr ('xbar', 'foobar '); -> 0

LOCATE (substr, str), LOCATE (substr, str, pos)
The first syntax returns the first occurrence position of the substring substr. The second syntax returns the first occurrence position of the substring substr In the str string, starting at the pos. If substr is not in str, the return value is 0.

The Code is as follows:	Copy code
Mysql> select locate ('bar', 'foobarbar '); -> 4 Mysql> select locate ('xbar', 'foobar '); -> 0 Mysql> select locate ('bar', 'foobarbarbar ', 5 ); -> 7

This function supports multi-byte characters and is case sensitive only when at least one parameter is a binary string.

POSITION (substr IN str)
Returns the position of the substring substr that appears for the first time in the str string. If the substring substr does not exist in str, the return value is 0:

The Code is as follows:	Copy code
Mysql> select position ('bar', 'foobarbar '); -> 4 Mysql> select position ('xbar', 'foobar '); -> 0

Efficiency Test

The Code is as follows:	Copy code
SELECT * FROM 'O _ soft 'where locate ('d200', tid2)> 0

The query result returned by MySQL is null (zero rows ). (The query takes 0.0050 seconds)

The Code is as follows:	Copy code
SELECT * FROM 'O _ soft 'where instr ('d200', tid2)> 0

The query result returned by MySQL is null (zero rows ). (The query takes 0.0009 seconds)