SQL Reference

LOCATE

>>-LOCATE--(--expression1--,--expression2--+-----------------+---)-->
                                           '-,--expression3--'
 
>--------------------------------------------------------------><
 

The schema is SYSFUN.

Returns the starting position of the first occurrence of expression1 within expression2. If the optional expression3 is specified, it indicates the character position in expression2 at which the search is to begin. If expression1 is not found within expression2, the value 0 is returned.

If the first argument is a character string, then the second argument must be a character string. For a VARCHAR the maximum length is 4 000 bytes and for a CLOB the maximum length is 1 048 576 bytes. If the first argument is a binary string, then the second argument must be a binary string with a maximum length of 1 048 576 bytes. The third argument must be is INTEGER or SMALLINT.

The result of the function is INTEGER. The result can be null; if any argument is null, the result is the null value.

Example:

• Find the location of the letter 'N' (first occurrence) in the word 'DINING'.

  VALUES LOCATE ('N', 'DINING')
  

  This example returns the following:

  1          
  -----------
            3