Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

LOCATE function (ODBC compatible)


LOCATE( char-expr1 , char-expr2, [start-position] )


The scalar function LOCATE returns the location of the first occurrence of char-expr1 in char-expr2. If the function includes the optional integer argument start-position, LOCATE begins searching char-expr2 at that position. If the function omits the start-position 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.

  • char_expr1 and char_expr2 must evaluate to a CHAR value.
  • start, if supplied, must evaluate to an INTEGER value.
  • If either expression is null, LOCATE returns NULL.


The following example uses two string literals as character expressions. LOCATE returns a value of six:

SELECT LOCATE('this', 'test this test', 1) FROM TEST;




1 record selected