LOCATE function [String]

Returns the position of one string within another.

LOCATE ( string-expression1, string-expression2
[ , numeric-expression ] )

The numeric-expression is a 32 bit signed integer for CHAR, VARCHAR, and BINARY columns.

The following statement returns the value 8:

SELECT LOCATE( 'office party this week – rsvp as soon as possible', 'party', 2 ) FROM iq_dummy

In the second example, the numeric-expression starting offset for the search is a negative number.

CREATE TABLE t1(name VARCHAR(20), dirname VARCHAR(60));
  INSERT INTO t1     VALUES(‘m1000’,’c:\test\functions\locate.sql’);
  INSERT INTO t1     VALUES(‘m1001’,’d:\test\functions\trim.sql’);
COMMIT;

SELECT LOCATE(dirname, ‘\’, -1), dirname FROM t1;

The result is:

18   c:\test\functions\locate.sql
18   d:\test\functions\trim.sql

If numeric-expression is specified, the search starts at that offset into the string being searched.

If numeric-expression is not specified, LOCATE returns only the position of the first instance of the specified string.

The first string can be a long string (longer than 255 bytes), but the second is limited to 255 bytes. If a long string is given as the second argument, the function returns a NULL value.

If any of the arguments is NULL, the result is NULL.

Searching for a zero-length string returns 1.

If the string does not contain the specified string, the LOCATE function returns zero (0).

All the positions or offsets, returned or specified, in the LOCATE function are always character offsets and may be different from the byte offset for multibyte data.