Skip to main content

POSITION

Description

The POSITION function is used to find the position of a substring in a main string, with positions counting from 1.

Syntax

POSITION(<substr> IN <str>)

POSITION(<substr>, <str> [, <pos>])

Parameters

ParameterDescription
substrThe substring to search for. Type: VARCHAR
strThe main string to search within. Type: VARCHAR
posOptional parameter specifying the starting position (from 1). If specified, search starts from this position

Return Value

Returns INT type, representing the position of the first occurrence of the substring in the main string.

Search rules:

  • Returns position index starting from 1
  • If substring is not found, returns 0
  • If a starting position is specified, searches from that position
  • Search is case-sensitive

Special cases:

  • If any parameter is NULL, returns NULL
  • If substring is an empty string, returns 1 (or the starting position)
  • If starting position exceeds string length, returns 0
  • If starting position is negative, searches from the beginning of the string

Examples

  1. Basic search (two syntax forms)
SELECT POSITION('bar' IN 'foobarbar'), POSITION('bar', 'foobarbar');
+----------------------------------+--------------------------------+
| POSITION('bar' IN 'foobarbar') | POSITION('bar', 'foobarbar') |
+----------------------------------+--------------------------------+
| 4 | 4 |
+----------------------------------+--------------------------------+
  1. Search with starting position
SELECT POSITION('bar', 'foobarbar', 5), POSITION('xbar', 'foobar');
+-----------------------------------+----------------------------------+
| POSITION('bar', 'foobarbar', 5) | POSITION('xbar', 'foobar') |
+-----------------------------------+----------------------------------+
| 7 | 0 |
+-----------------------------------+----------------------------------+
  1. NULL value handling
SELECT POSITION('test' IN NULL), POSITION(NULL, 'test');
+--------------------------+------------------------+
| POSITION('test' IN NULL) | POSITION(NULL, 'test') |
+--------------------------+------------------------+
| NULL | NULL |
+--------------------------+------------------------+
  1. Empty string handling
SELECT POSITION('' IN 'hello'), POSITION('world' IN '');
+------------------------+------------------------+
| POSITION('' IN 'hello') | POSITION('world' IN '') |
+------------------------+------------------------+
| 1 | 0 |
+------------------------+------------------------+
  1. Case-sensitive search
SELECT POSITION('World' IN 'Hello World'), POSITION('world' IN 'Hello World');
+----------------------------------+----------------------------------+
| POSITION('World' IN 'Hello World') | POSITION('world' IN 'Hello World') |
+----------------------------------+----------------------------------+
| 7 | 0 |
+----------------------------------+----------------------------------+
  1. Search from different positions
SELECT POSITION('a', 'banana', 1), POSITION('a', 'banana', 3);
+-----------------------------+-----------------------------+
| POSITION('a', 'banana', 1) | POSITION('a', 'banana', 3) |
+-----------------------------+-----------------------------+
| 2 | 4 |
+-----------------------------+-----------------------------+
  1. UTF-8 multi-byte characters
SELECT POSITION('ḍḍumai' IN 'ṭṛì ḍḍumai hello'), POSITION('hello', 'ṭṛì ḍḍumai hello', 8);
+--------------------------------------+-------------------------------------------+
| POSITION('ḍḍumai' IN 'ṭṛì ḍḍumai hello') | POSITION('hello', 'ṭṛì ḍḍumai hello', 8) |
+--------------------------------------+-------------------------------------------+
| 5 | 13 |
+--------------------------------------+-------------------------------------------+
  1. Special character search
SELECT POSITION('@' IN 'user@domain.com'), POSITION('.', 'user@domain.com', 10);
+----------------------------------+--------------------------------------+
| POSITION('@' IN 'user@domain.com') | POSITION('.', 'user@domain.com', 10) |
+----------------------------------+--------------------------------------+
| 5 | 12 |
+----------------------------------+--------------------------------------+
  1. Starting position beyond bounds
SELECT POSITION('test', 'hello world', 20), POSITION('test', 'hello world', 0);
+--------------------------------------+-------------------------------------+
| POSITION('test', 'hello world', 20) | POSITION('test', 'hello world', 0) |
+--------------------------------------+-------------------------------------+
| 0 | 0 |
+--------------------------------------+-------------------------------------+
  1. Search in numbers and symbols
SELECT POSITION('123' IN '456123789'), POSITION('-', 'phone: 123-456-7890', 11);
+------------------------------+-------------------------------------------+
| POSITION('123' IN '456123789') | POSITION('-', 'phone: 123-456-7890', 11) |
+------------------------------+-------------------------------------------+
| 4 | 11 |
+------------------------------+-------------------------------------------+

Description

The POSITION function is used to find the position of a substring within a string (counting from 1).

If the substring is not found, the function returns 0.

Syntax

POSITION ( <substr> IN <str> )

POSITION ( <substr>, <str> [, <pos>] )

Parameters

ParameterDescription
substrThe substring to search for
strThe string to be searched
posIf this parameter is specified, the position of substr is searched from the string starting with the pos subscript

Return value

The position of substr in str (counting from 1). If substr is not found, returns 0.

SELECT POSITION('bar' IN 'foobarbar'), 
POSITION('bar', 'foobarbar'),
POSITION('bar', 'foobarbar', 5),
POSITION('xbar', 'foobar');

+----------------------------------+--------------------------------+-----------------------------------+----------------------------------+
| position('bar' in 'foobarbar') | position('bar', 'foobarbar') | position('bar', 'foobarbar', 5) | position('xbar', 'foobar') |
+----------------------------------+--------------------------------+-----------------------------------+----------------------------------+
| 4 | 4 | 7 | 0 |
+----------------------------------+--------------------------------+-----------------------------------+----------------------------------+