The LOCATE and POSITION functions in MySQL are used
- 2020-05-10 23:00:16
- OfStack
LOCATE(substr,str)
POSITION(substr IN str)
Returns the first occurrence of the substring substr in the string str. If the substring substr does not exist in str, the return value is 0:
mysql > SELECT LOCATE (' bar ', 'foobarbar');
- > 4
mysql > SELECT LOCATE (' xbar ', 'foobar');
- > 0
This function is multi-byte safe. In MySQL 3.23, this function is case sensitive, and in MySQL 4.0, it is case sensitive if any 1 parameter is a 2-base string.
LOCATE(substr,str,pos)
Returns the first occurrence of the substring substr after the pos position in the string str. If substr does not return 0 in str:
mysql > SELECT LOCATE (' bar ', 'foobarbar', 5);
- > 7
This function is multi-byte safe. In MySQL 3.23, this function is case sensitive, and in MySQL 4.0, it is case sensitive if any 1 parameter is a 2-base string.
A typical example is:
slect * from tablename where LOCATE(colum1,colum1,1) > 0
POSITION(substr IN str)
Returns the first occurrence of the substring substr in the string str. If the substring substr does not exist in str, the return value is 0:
mysql > SELECT LOCATE (' bar ', 'foobarbar');
- > 4
mysql > SELECT LOCATE (' xbar ', 'foobar');
- > 0
This function is multi-byte safe. In MySQL 3.23, this function is case sensitive, and in MySQL 4.0, it is case sensitive if any 1 parameter is a 2-base string.
LOCATE(substr,str,pos)
Returns the first occurrence of the substring substr after the pos position in the string str. If substr does not return 0 in str:
mysql > SELECT LOCATE (' bar ', 'foobarbar', 5);
- > 7
This function is multi-byte safe. In MySQL 3.23, this function is case sensitive, and in MySQL 4.0, it is case sensitive if any 1 parameter is a 2-base string.
A typical example is:
slect * from tablename where LOCATE(colum1,colum1,1) > 0