Skip to main content

locate

1. LOCATE Function​

The LOCATE function in H2 database is used to find the position of a substring within a string.

2. Syntax​

The syntax for the LOCATE function in H2 database is as follows:

LOCATE(substring, string [, start])

Arguments​

  • substring: The substring that you want to search for within the string.
  • string: The string in which you want to search for the substring.
  • start (optional): The position in the string where the search should start. If not specified, the search starts from the beginning of the string.

Return​

  • The LOCATE function returns the position of the first occurrence of the substring within the string. If the substring is not found, it returns 0.

3. Notes​

  • The comparison is case-sensitive, so the LOCATE function distinguishes between uppercase and lowercase characters.
  • If the input arguments are NULL, the LOCATE function will return NULL.
  • When using the LOCATE function, ensure that you provide the correct sequence of arguments to avoid unexpected results or errors.

4. Examples​

Here are a few examples demonstrating the usage of the LOCATE function in H2 database:

Example 1 - Finding the position of a substring within a string:​

SELECT LOCATE('world', 'Hello world!') AS position;

Output:

position
--------
7

Example 2 - Specifying the starting position for the search:​

SELECT LOCATE('o', 'Hello world!', 5) AS position;

Output:

position
--------
8

Example 3 - Searching for a substring that is not present in the string:​

SELECT LOCATE('foo', 'Hello world!') AS position;

Output:

position
--------
0
  • POSITION - Alias for the LOCATE function (supported by some other databases)
  • INSTR - Find the position of a substring within a string (supported by some other databases)