	©	SQL :: Manager
				Home	Program Information	Technical details	Free download	Donate for a cup of coffee

The FIND_IN_SET SQL Function in MySQL and MariaDB - Position in set of values

FIND_IN_SET	Syntax:	FIND_IN_SET(Search string, Set of values)
	Return value:	INTEGER
	Function type:	String function

Function Description

The FIND_IN_SET() function returns the index position at which the specified pattern "Search string" occurs in the string list "Set of values".

"Set of values" is a string containing comma-separated values.

If "Set of values" is of data type SET, the function is optimized to use bit arithmetic.

If the pattern "Search string" does not occur in the "Set of values" or if the "Set of values" is an empty string, the function returns 0.

Numeric values in "Search string" are interpreted as character strings.

If either the "Search string" or "Set of values" argument is NULL, the FIND_IN_SET() function returns NULL.

The FIND_IN_SET() function returns 0 if "Search string" contains a comma.

SQL Examples for the FIND_IN_SET function


select find_in_set('1', '1,2,3,4,5,6,7,8,9');
select find_in_set('5', '1,2,3,4,5,6,7,8,9');
select find_in_set(1, '1,2,3,4,5,6,7,8,9');
select find_in_set(5, '1,2,3,4,5,6,7,8,9');

select find_in_set('test2', 'test1,test2,test3,test4');
select find_in_set('test4', 'test1,test2,test3,test4');
select find_in_set('test5', 'test1,test2,test3,test4');

select find_in_set('test4', '');
select find_in_set('test2,test3', 'test1,test2,test3,test4');

select find_in_set(null, 'test1,test2,test3,test4');
select find_in_set('test', null);

find_in_set('1', '1,2,3,4,5,6,7,8,9')
int(3)
1

find_in_set('5', '1,2,3,4,5,6,7,8,9')
int(3)
5

find_in_set(1, '1,2,3,4,5,6,7,8,9')
int(3)
1

find_in_set(5, '1,2,3,4,5,6,7,8,9')
int(3)
5

find_in_set('test2', 'test1,test2,test3,test4')
int(3)
2

find_in_set('test4', 'test1,test2,test3,test4')
int(3)
4

find_in_set('test5', 'test1,test2,test3,test4')
int(3)
0

find_in_set('test4', '')
int(3)
0

find_in_set('test2,test3', 'test1,test2,test3,test4')
int(3)
0

find_in_set(null, 'test1,test2,test3,test4')
int(3)
NULL

find_in_set('test', null)
int(3)
NULL

The examples were created with the MyWAY SQL manager: Download

How to use the FIND_IN_SET() function in MySQL and MariaDB databases

In MySQL and MariaDB, the FIND_IN_SET() function can be used to look for a value in a comma-separated list of values. The function returns the position of the value within the list if found, or 0 if not found, and can thus be used, for example, to perform searches or filters based on values contained in a saved comma-delimited list.

Further MySQL und MariaDB SQL String functions

CHAR	CHAR(Character code, [, Charset])
	More about CHAR Function

CHR	CHR(Character code)
	More about CHR Function

CONCAT	CONCAT(Strings ('str1','str2', ...))
	More about CONCAT Function

CONCAT_WS	CONCAT_WS(Separator, Strings ('str1','str2', ...))
	More about CONCAT_WS Function

ELT	ELT(Index, Strings ('str1','str2', ...))
	More about ELT Function

EXPORT_SET	EXPORT_SET(Bits, On, Off [, Separator] [, Number of bits])
	More about EXPORT_SET Function

EXTRACTVALUE	EXTRACTVALUE(XML, XPath)

FIELD	FIELD(Search string, Strings ('str1','str2', ...))
	More about FIELD Function

FORMAT	FORMAT(Number, Decimals [, Region])
	More about FORMAT Function

INSERT	INSERT(String, Position, Length, String to insert)
	More about INSERT Function

LCASE	LCASE(String)
	More about LCASE Function

LEFT	LEFT(String, Length)
	More about LEFT Function

LOAD_FILE	LOAD_FILE(File name)

LOWER	LOWER(String)
	More about LOWER Function

LPAD	LPAD(String, Length, String to append)
	More about LPAD Function

LPAD_ORACLE	LPAD_ORACLE(String, Length, String to append)
	More about LPAD_ORACLE Function

LTRIM	LTRIM(String)
	More about LTRIM Function

MAKE_SET	MAKE_SET(Bits, Strings ('str1','str2', ...))
	More about MAKE_SET Function

MID	MID(String, Position [, Length])
	More about MID Function

QUOTE	QUOTE(String)
	More about QUOTE Function

REPEAT	REPEAT(String, Number)
	More about REPEAT Function

REPLACE	REPLACE(String, Search, Replace)
	More about REPLACE Function

REVERSE	REVERSE(String)
	More about REVERSE Function

RIGHT	RIGHT(String, Length)
	More about RIGHT Function

RPAD	RPAD(String, Length, String to append)
	More about RPAD Function

RPAD_ORACLE	RPAD_ORACLE(String, Length, String to append)
	More about RPAD_ORACLE Function

RTRIM	RTRIM(String)
	More about RTRIM Function

SOUNDEX	SOUNDEX(String)
	More about SOUNDEX Function

SPACE	SPACE(Number)
	More about SPACE Function

SUBSTR	SUBSTR(String, Position, Length)
	More about SUBSTR Function

SUBSTRING	SUBSTRING(String, Position, Length)
	More about SUBSTRING Function

SUBSTRING_INDEX	SUBSTRING_INDEX(String, Delimiter, Number)
	More about SUBSTRING_INDEX Function

TRIM	TRIM([Type] [, Delete character], , String)
	More about TRIM Function

TRIM_ORACLE	TRIM_ORACLE([Type] [, Delete character], , String)
	More about TRIM_ORACLE Function

UCASE	UCASE(String)
	More about UCASE Function

UPPER	UPPER(String)
	More about UPPER Function

WEIGHT_STRING	WEIGHT_STRING(String [AS Format] [LEVEL Level] [Sorting])
	More about WEIGHT_STRING Function

ASCII	ASCII(String)
	More about ASCII Function

BIT_LENGTH	BIT_LENGTH(String)
	More about BIT_LENGTH Function

CHAR_LENGTH	CHAR_LENGTH(String)
	More about CHAR_LENGTH Function

CHARACTER_LENGTH	CHARACTER_LENGTH(String)
	More about CHARACTER_LENGTH Function

INSTR	INSTR(String, Search string)
	More about INSTR Function

LENGTH	LENGTH(String)
	More about LENGTH Function

LENGTHB	LENGTHB(String)
	More about LENGTHB Function

LOCATE	LOCATE(Search string, String [, Position])
	More about LOCATE Function

OCTET_LENGTH	OCTET_LENGTH(String)
	More about OCTET_LENGTH Function

ORD	ORD(String)
	More about ORD Function

POSITION	POSITION(Search string IN String)
	More about POSITION Function

Numeric functions	String functions	Regular Expressions	Date and Time functions
Comparison functions	Encryption & Compression	Conversion functions	NULL functions
Aggregate functions	Window functions	JSON functions	Geometric functions
Sequence functions	Information functions	Dynamic Columns	Miscellaneous functions

More information about the FIND_IN_SET SQL function: mysql.com and mariadb.com and w3schools.com

Updated: 2023-09-17

2020-2024 Heino Cunze-Fischer