Returns the location of the first occurrence of char_expr1 in char_expr2. If the function includes the optional integer argument start_pos, LOCATE begins searching char_expr2 at that position. If the function omits the start_pos argument, LOCATE begins its search at the beginning of char_expr2.

LOCATE denotes the first character position of a character expression as 1. If the search fails, LOCATE returns 0. If either character expression is NULL, LOCATE returns a NULL value.

Syntax

LOCATE( char_expr1 , char_expr2 , [start_pos] )

Notes

char_expr1 and char_expr2 can contain multi‑byte characters. The start_pos argument specifies the position of a starting character, not a byte position. The search is case sensitive. Character comparisons use the collation table in the database.

Example

In the following example, which uses two string literals as character expressions, LOCATE returns a value of 6:

SELECT LOCATE('this', 'test this test', 1) FROM TEST;
 LOCATE(THIS,
------------ 
61 record selected

Compatibility

ODBC compatible