MySQL8 FIND_IN_SET() Functions – String Functions

by · Published · Updated

MySQL FIND_IN_SET() Functions: Syntax

FIND_IN_SET ( searchString, stringList );

MySQL FIND_IN_SET() Functions: Description

FIND_IN_SET() function searches for the specified string in the strings SET argument. If the search string is found then its index position will be returned else 0 will be returned.

A string list is a collection of strings composed of substrings separated by Comma(,) characters.

Returns a value in the range of 1 to N if the searchString is in the stringList consisting of N substrings.

If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic.

Note : This function does not work properly if the first argument contains a comma (,) character.

MySQL FIND_IN_SET() Functions: Parameter

[table caption=”” width=”100%” colwidth=”15%|30%|55%” colalign=”left|left|left”]
Name, Required /Optional, Description
searchString, Required, The search string.
stringList, Required, The list of string values seprated by comma character.
[/table]

MySQL FIND_IN_SET() Functions: Output

[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
0, if searchString is not found in the stringList.
0, if stringList contains no elements i.e empty string.
NULL, if searchString or stringList any of the argument is NULL.
value in the range 1 to N, if the searchString is in the stringList consisting of N substrings.
[/table]

MySQL FIND_IN_SET() Functions: Available from

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

 

MySQL FIND_IN_SET() Functions: Example 1

mysql>  SELECT FIND_IN_SET('.net','Tutorial,mines,.net');
+---------------------------------------------------+
| FIND_IN_SET('.net','Tutorial,mines,.net') |
+---------------------------------------------------+
| 3                           |
+---------------------------------------------------+

MySQL FIELD() Functions: Example 2

mysql> SELECT FIND_IN_SET('www','Tutorial,mines,.net');
+----------------------------------------------------+
| FIND_IN_SET('www','Tutorial,mines,.net')|
+----------------------------------------------------+
| 0                               |
+-----------------------------------------------------+

MySQL FIELD() Functions: Example 3

mysql>  SELECT  FIND_IN_SET('NULL','11,22,33') ;
+---------------------------------------+
| FIND_IN_SET('NULL','11,22,33') |
+---------------------------------------+
| 0                              |
+---------------------------------------+

MySQL FIELD() Functions: Example 4

mysql>  SELECT FIND_IN_SET('6','');
+------------------------+
| FIND_IN_SET('6','') |
+------------------------+
| 0                      |
+------------------------+

See all MySQL String functions MySQL 8 String Functions.

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

PHP Related articles : IMPLODE(), STRPOS(), STRRPOS(), PHP STRING FUNCTIONS(), STRRIPOS().

You may also like...