POSITION

​

Overview

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 REPLACE() function syntax is as follows.

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');

123 is found starting at position 1, the result would be 1.

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

​

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