LOCATE

Purpose

This function returns the position in string at which search_string appears. If this is not contained, the value 0 is returned.

Syntax

locate::=

Usage Notes

  • The optional parameter position defines from which position the search shall begin (starting with 1)
  • If the value is negative, Exasol counts and searches backwards from the end, for example, LOCATE('abc',string,- 3) searches backwards from the third last letter.
  • LOCATE(search_string,string) is similar to LOCATE(search_string,string,1).
  • The functions POSITION and INSTR are similar.

Example

SELECT LOCATE('cab','abcabcabc') LOCATE1,
       LOCATE('user','user1,user2,user3,user4,user5', -1) LOCATE2;
Result
LOCATE1 LOCATE2
3 25