![LENGTH function [String]](arrow-left.gif){kind=icon}
![LOG function [Numeric]](arrow-right.gif){kind=icon}
Returns the position of one string within another.
LOCATE ( string-expression1, string-expression2 [, numeric-expression ] )
string-expression1 The string to be searched.
string-expression2 The string to be searched for. This string is limited to 255 bytes.
numeric-expression The character position at which to begin the search in the string. The first character is position 1. If the starting offset is negative, LOCATE returns the last matching string offset, rather than the first. A negative offset indicates how much of the end of the string to exclude from the search. The number of bytes excluded is calculated as ( -1 * offset ) - 1.
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.
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 the string is not found, 0 is returned. Searching for a zero-length string returns 1. If any of the arguments are NULL, the result is NULL.
If multibyte characters are used, with the appropriate collation, then the starting position and the return value may be different from the byte positions.
