LOCATE Function [String]

Returns the position of one string within another.

Syntax

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

Parameters

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.

Returns

INT

Remarks

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. A second string longer than 255 bytes causes an error.

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 LOCATE Function in Unstructured Data Analytics.

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server.

Example

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