• CONCAT_WS( separator , str1 , str2 ,...) 

CONCAT_WS()  stands for Concatenate With Separator and is a special form of CONCAT() . The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL , the result is NULL . 

mysql> SELECT CONCAT_WS(’,’,’First name’,’Second name’,’Last Name’); -> ’First name,Second name,Last Name’ mysql> SELECT CONCAT_WS(’,’,’First name’,NULL,’Last Name’); -> ’First name,Last Name’ 

CONCAT_WS()  does not skip empty strings. However, it does skip any NULL  values after the separator argument. 

• ELT( N , str1 , str2 , str3 ,...) 

ELT()  returns the N th element of the list of strings: str1  if N  = 1 , str2  if N  = 2 , and so on. Returns NULL  if N  is less than 1  or greater than the number of arguments. ELT()  is the complement of FIELD() . 

mysql> SELECT ELT(1, ’Aa’, ’Bb’, ’Cc’, ’Dd’); -> ’Aa’ mysql> SELECT ELT(4, ’Aa’, ’Bb’, ’Cc’, ’Dd’); -> ’Dd’ 

• EXPORT_SET( bits , on , off [, separator [, number_of_bits ]]) 

Returns a string such that for every bit set in the value bits , you get an on  string and for every bit not set in the value, you get an off  string. Bits in bits  are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator  string (the default being the comma character , ). The number of bits examined is given by number_of_bits , which has a default of 64 if not specified. number_of_bits  is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of − 1 is effectively the same as 64. 

mysql> SELECT EXPORT_SET(5,’Y’,’N’,’,’,4); -> ’Y,N,Y,N’ mysql> SELECT EXPORT_SET(6,’1’,’0’,’,’,10); -> ’0,1,1,0,0,0,0,0,0,0’ 

• FIELD( str , str1 , str2 , str3 ,...) 

Returns the index (position) of str  in the str1 , str2 , str3 , ...  list. Returns 0  if str  is not found. 

If all arguments to FIELD()  are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double. 

If str  is NULL , the return value is 0  because NULL  fails equality comparison with any value. FIELD()  is the complement of ELT() . 

mysql> SELECT FIELD(’Bb’, ’Aa’, ’Bb’, ’Cc’, ’Dd’, ’Ff’); -> 2 mysql> SELECT FIELD(’Gg’, ’Aa’, ’Bb’, ’Cc’, ’Dd’, ’Ff’); -> 0 

• FIND_IN_SET( str , strlist ) 

Returns a value in the range of 1 to N  if the string str  is in the string list strlist  consisting of N  substrings. A string list is a string composed of substrings separated by ,  characters. 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. Returns 0  if str  is not in strlist  or if strlist  is the empty string. 

