MySQL8 POSITION() Functions – String Functions

by · Published · Updated

POSITION() Functions return the position of the first instance of srchstring in string .

Returns 0 if srchstring is not in string. Returns NULL if any argument is NULL.

Note : This function is equal to the LOCATE() function with a small change in syntax.

Note : This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

MySQL POSITION() Functions: Syntax

POSITION ( srchstring  IN string );
 

MySQL POSITION() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|15%|15%|55%” colalign=”left|left|left|left”]
Name, Required /Optional,Type, Description
string, Required, String , It represents the valid input string.
srchstring, Required, String , It represents valid srchstring to be search for.
[/table]

MySQL POSITION() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Return, Description
Number, the position of the first instance of srchstring in string.
0, If srchstring is not found in the string.
NULL, if any argument is NULL.[/table]

MySQL POSITION() Functions: Available from

[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]

POSITION() Example 1 : Very easy example

We have to search for srchstring i.e ‘site’ in input string i.e ‘This is tutorialmines site for all!’. Below is the example to show it.

mysql> SELECT POSITION('site' IN 'This is tutorialmines site for all!');
+-----------------------------------------------------------+
| POSITION('site' IN 'This is tutorialmines site for all!') |
+-----------------------------------------------------------+
|                                                        23 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

POSITION() Example 2 : Searching Part of a Word

Below is the example where we will find ‘mines‘, in the word ‘tutorialmines‘. This function will find the position of the first instance of srchstring in string.

mysql> SELECT POSITION('mines' IN 'This is tutorialmines site for all!');
+------------------------------------------------------------+
| POSITION('mines' IN 'This is tutorialmines site for all!') |
+------------------------------------------------------------+
| 17 |
+------------------------------------------------------------+
1 row in set (0.01 sec)

POSITION() Example 3 : No Match found

If the srchstring  i.e ‘java’ isn’t found, 0 is returned:

mysql> SELECT POSITION('java' IN 'This is tutorialmines site for all!');
+-----------------------------------------------------------+
| POSITION('java' IN 'This is tutorialmines site for all!') |
+-----------------------------------------------------------+
|                                                         0 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

POSITION() Example 4 : Similar result, whether search string is in lower case or uppercase.

mysql> SELECT POSITION('MINES' IN 'tutorialmines!');
+---------------------------------------+
| POSITION('MINES' IN 'tutorialmines!') |
+---------------------------------------+
|                                     9 |
+---------------------------------------+
1 row in set (0.00 sec)

POSITION() Example 5 : NULL arguments

If any of the arguments is NULL, it will return NULL. See below example

mysql> SELECT POSITION('This is tutorialmines site!' IN NULL) as First,
       POSITION(NULL IN 'mines') as Second;
+-------+--------+
| First | Second |
+-------+--------+
|  NULL |   NULL |
+-------+--------+
1 row in set (0.00 sec)

See all MySQL String functions MySQL 8 String Functions.

Related articles : LOCATE(), HEX(), CONCAT(), CONCAT_WS() , LOWER(), LTRIM(), INSTR(), POSITION().

PHP Related articles : SUBSTR(), SUBSTR_COUNT(), SUBSTR_COMPARE(), SUBSTR_REPLACE(), PHP STRING FUNCTIONS().

 

You may also like...