LOCATE function

Parameters: substr – – String to query STR – – field name pos – – initial search position Returned value: returns the position where substr first appears in STR. 0 is not returned

PS: In MySQL 4.0, if any argument is a binary string, it is case sensitive

Grammar: LOCATE (substr STR)

SELECT LOCATE('bar'.'foobarbar');   //Results:4
SELECT LOCATE('xbar'.'foobar');	//Results:0
Copy the code

LOCATE(substr, STR,pos) returns the STR position in the string substr where the first substring appears, starting pos. 0 If substr is not present, STR is returned. Returns NULL if substr or STR is NULL. Note: POS must be greater than the first occurrence in order to display the second occurrence

SELECT LOCATE('bar'.'foobarbar'.5);	//Results:7
Copy the code

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

POSITION()

Parameters: substr – – String to query STR – – field name

Return value: Returns the first occurrence of the string being queried within the string being queried.

Returns the substr string at the position where STR appears, without returning 0

Usage: POSITION(substr IN STR)

SELECT POSITION('cn' IN 'aaaaacn');		//Results:6
Copy the code

INSTR()

Return value: Returns the position where the string to be queried first appears in the queried string. This is the same two-parameter form as LOCATE(), except that the order of the arguments is reversed.

Syntax: INSTR(STR,substr) returns the substr string at the location where STR appears, without returning 0

SELECT INSTR('aaaacom'.'com'); 	//Results:5
Copy the code

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 set

SELECT FIND_IN_SET('demo.com.cn',t.str) FROM `table` t;
Copy the code

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 set

SELECT 'demo.com.cn' IN(t.str)  FROM `table` t;
Copy the code

LIKE

Returns a collection of similar (fuzzy) characters

LIKE % STR % returns a collection similar to STR