Cloud

POSITION

The POSITION function returns the position of the first occurrence of a substring in a string. It works the same as strpos(), but it has slightly different syntax.

Syntax

The syntax for this function is:

POSITION(substring IN string)

The position of the substring within the string starts from 1. If the substring is not found, it returns 0.

Examples

Example 1

This query looks for the position of the substring world within the string Hello, world!.

SELECT POSITION('world' IN 'Hello, world!');

The result would be the starting position of the substring world, which is 7.

position
----------
        7

Example 2

The query looks for the position of the substring 123 within the string 1a2b3c.

SELECT POSITION('123' IN '1a2b3c');

The substring 123 does not appear contiguously in 1a2b3c, so the result is 0.

position
----------
        0

Example 3

The query tries to find the position of the substring abc within the string xyz.

SELECT POSITION('abc' IN 'xyz');

abc is not found in xyz, the result would be 0.

position
----------
        0

Example 4

This query searches for the position of the substring cde within the string cde.

SELECT POSITION('cde' IN 'cde');

cde is the entire string, the result would be 1.

position
----------
        1