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
| Parameter | Description |
|---|---|
substr | The substring to search for. Type: VARCHAR |
str | The main string to search within. Type: VARCHAR |
pos | Optional 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
- Basic search (two syntax forms)
SELECT POSITION('bar' IN 'foobarbar'), POSITION('bar', 'foobarbar');
+----------------------------------+--------------------------------+
| POSITION('bar' IN 'foobarbar') | POSITION('bar', 'foobarbar') |
+----------------------------------+--------------------------------+
| 4 | 4 |
+----------------------------------+--------------------------------+
- Search with starting position
SELECT POSITION('bar', 'foobarbar', 5), POSITION('xbar', 'foobar');
+-----------------------------------+----------------------------------+
| POSITION('bar', 'foobarbar', 5) | POSITION('xbar', 'foobar') |
+-----------------------------------+----------------------------------+
| 7 | 0 |
+-----------------------------------+----------------------------------+
- NULL value handling
SELECT POSITION('test' IN NULL), POSITION(NULL, 'test');
+--------------------------+------------------------+
| POSITION('test' IN NULL) | POSITION(NULL, 'test') |
+--------------------------+------------------------+
| NULL | NULL |
+--------------------------+------------------------+
- Empty string handling
SELECT POSITION('' IN 'hello'), POSITION('world' IN '');
+------------------------+------------------------+
| POSITION('' IN 'hello') | POSITION('world' IN '') |
+------------------------+------------------------+
| 1 | 0 |
+------------------------+------------------------+
- 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 |
+----------------------------------+----------------------------------+
- Search from different positions
SELECT POSITION('a', 'banana', 1), POSITION('a', 'banana', 3);
+-----------------------------+-----------------------------+
| POSITION('a', 'banana', 1) | POSITION('a', 'banana', 3) |
+-----------------------------+-----------------------------+
| 2 | 4 |
+-----------------------------+-----------------------------+
- 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 |
+--------------------------------------+-------------------------------------------+
- 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 |
+----------------------------------+--------------------------------------+
- 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 |
+--------------------------------------+-------------------------------------+
- 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
| Parameter | Description |
|---|---|
substr | The substring to search for |
str | The string to be searched |
pos | If 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 |
+----------------------------------+--------------------------------+-----------------------------------+----------------------------------+
