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