Returns the position of one string within another.
Parameter |
Description |
|---|---|
string-expression1 |
The string to be searched. |
string-expression2 |
The string for which you are searching. 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 numeric-expression is a 32 bit signed integer for CHAR, VARCHAR, and BINARY columns.
INT
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.
If you are licensed to use the Unstructured Data Analytics functionality, you can use this function with large object data.
See Unstructured Data Analytics in Sybase IQ > Function Support.