Umesh Chandra

---

1. FIELD(str, str1, str2, . . .)

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.

Code:

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

---

2. 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. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.

Code:

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

---

3. LCASE(str)

LCASE() is a synonym for LOWER().

Returns the string str with all characters changed to lowercase according to the current character set mapping, or NULL if str is NULL. The default character set is utf8mb4.

LCASE() used in a view is rewritten as LOWER() when storing the view’s definition.

Code:

mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'

mysql> SELECT LCASE('New York');
+-------------------+
| LCASE('New York') |
+-------------------+
| new york          |
+-------------------+

---

4. LEFT(str,len)

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

Code:

mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'

---

5. LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str.

The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if any argument is NULL.

Code:

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

---

6. LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

Code:

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'

Returns NULL if any of its arguments are NULL.

---

7. RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. If str, padstr, or len is NULL, the function returns NULL.

Code:

mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'

---

8. LTRIM(str)

Returns the string str with leading space characters removed. Returns NULL if str is NULL.

Code:

mysql> SELECT LTRIM('  barbar');
        -> 'barbar'

---

9. RTRIM(str)

Returns the string str with trailing space characters removed.

Code:

mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'

This function is multibyte safe, and returns NULL if str is NULL.

---

10. QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote ('), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.

Code:

mysql> SELECT QUOTE('Don\'t!');
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL

---

11. RIGHT(str,len)

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

Code:

mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'

---

12. LEFT(str,len)

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

Code:

mysql> SELECT LEFT('foobarbar', 4);
        -> 'foob'