The method of determining whether a string is in a specified field by SQL

LOCATE function

Parameters:

Substr-the string to be queried

Str-Field name

Pos-start search location

Return value:

Returns the position where substr first appeared in str, and did not return 0

PS: when in MySQL 4.0, if any parameter is a binary string, it is case-sensitive

Syntax:

LOCATE (substr,str)

SELECT LOCATE ('bar',' foobarbar'); / / result: 4

SELECT LOCATE ('xbar',' foobar'); / / result: 0

LOCATE (substr,str,pos)

Returns the str position of the first occurrence of the substring in the string substr, starting with pos. 0 returns str if substr is not present. Returns NULL if substr or str is NULL.

Note: the pos must be greater than the location of the first occurrence in order to display the location of the second occurrence

SELECT LOCATE ('bar',' foobarbar', 5); / / result: 7

When it is less than or equal to the first occurrence position (5), the first occurrence position is returned.

POSITION ()

Parameters:

Substr-the string to be queried

Str-Field name

Return value:

Returns the position of the string to be queried for the first time in the queried string (as in locate usage, position is an alias for locate)

Returns the position where the substr string appears in str, but does not return 0

Usage:

POSITION (substr IN str)

SELECT POSITION ('cn' IN' aaaaacn'); / / result: 6

INSTR ()

Return value: Wuxi passenger abortion fee http://www.xasgfk120.com/

Returns the position of the string to be queried for the first time in the queried string. This is the same as the two-parameter form of LOCATE (), except that the order of the parameters is reversed.

Syntax:

INSTR (str,substr)

Returns the position where the substr string appears in str, but does not return 0

SELECT INSTR ('aaaacom','com'); / / result: 5

FIND_IN_SET ()

Returns the index position in the collection (vertical development)

Syntax:

FIND_IN_SET (str,strlist)

Returns the index position of str1 in the strlist collection

SELECT FIND_IN_SET ('demo.com.cn',t.str) FROM `table`t

IN ()

Return value:

Returns the index position in the collection (same as FIND_IN_SET)

Syntax:

Str IN (strlist)

Returns the index position of str1 in the strlist collection

SELECT 'demo.com.cn' IN (t.str) FROM `table`t

LIKE

Returns a collection of similar (obscure) characters

LIKE str%

Returns a collection similar to str