MySQL - String Functions

MySQL String Functions and Operators

MySQL String Functions

MySQL provides a rich set of built-in functions for manipulating string values. This list serves as a comprehensive reference for common string functions and operators.

Sr.No.	Name & Description
1	ASCII(str) Returns the numeric ASCII code of the leftmost character in the string str.
2	BIN(N) Returns a string representation of the binary value of the integer N.
3	BIT_LENGTH(str) Returns the length of the string str in bits.
4	CHAR(N,… [USING charset]) Returns a string constructed from the ASCII/Unicode code points given as integer arguments.
5	CHAR_LENGTH(str) Returns the length of the string str in characters. This is the correct function to use for multi-byte character sets like UTF-8.
6	CHARACTER_LENGTH(str) A synonym for CHAR_LENGTH().
7	CONCAT(str1, str2,…) Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL.
8	CONCAT_WS(separator, str1, str2,…) Concatenate With Separator. Returns a concatenated string where arguments are separated by the separator. Skips any NULL arguments after the separator.
9	ELT(N, str1, str2, str3,…) Returns the Nth element of the list of strings. For example, ELT(2, 'a', 'b', 'c') returns ‘b’.
10	EXPORT_SET(bits, on, off[, separator[, number_of_bits]]) Returns a string where for each set bit in bits, you get the on string, and for each unset bit, you get the off string.
11	FIELD(str, str1, str2,…) Returns the 1-based index of str in the list of strings str1, str2, .... Returns 0 if str is not found.
12	FIND_IN_SET(str, str_list) Returns the 1-based index of str in str_list, where str_list is a comma-separated string. Returns 0 if not found.
13	FROM_BASE64(str) Decodes a base-64 encoded string and returns the result as a binary string.
14	INSERT(str, pos, len, newstr) Replaces the substring in str starting at position pos and len characters long with newstr.
15	INSTR(str, substr) Returns the 1-based starting position of the first occurrence of substr in str. Returns 0 if not found. Case-insensitive in default collations.
16	LCASE(str) A synonym for LOWER().
17	LEFT(str, len) Returns the leftmost len characters from the string str.
18	LENGTH(str) Returns the length of the string str in bytes. For multi-byte character sets, this may not equal the number of characters. Use CHAR_LENGTH() for character count.
19	LOAD_FILE(file_name) Reads the file and returns the file contents as a string. Requires the FILE privilege.
20	LOCATE(substr, str[, pos]) Returns the 1-based position of the first occurrence of substr in str, optionally starting the search at pos. A synonym for POSITION().
21	LOWER(str) Returns the string str with all characters changed to lowercase according to the current character set mapping.
22	LPAD(str, len, padstr) Left-pads the string str with the string padstr until str is len characters long.
23	LTRIM(str) Removes leading whitespace characters from the string str.
24	MAKE_SET(bits, str1, str2,…) Returns a set value (a string containing comma-separated substrings) consisting of the strings that have the corresponding bit in bits set.
25	MID(str, pos, len) A synonym for SUBSTRING(str, pos, len).
26	OCT(N) Returns a string representation of the octal value of the integer N.
27	OCTET_LENGTH(str) A synonym for LENGTH().
28	ORD(str) If the leftmost character of str is a single-byte character, returns its ASCII code. For multi-byte characters, it returns the Unicode code point.
29	POSITION(substr IN str) A synonym for LOCATE(substr, str).
30	QUOTE(str) Produces a result that can be used as a properly escaped data value in an SQL statement. Encloses the string in single quotes and escapes backslashes and single quotes.
31	REPEAT(str, count) Returns a string consisting of the string str repeated count times.
32	REPLACE(str, from_str, to_str) Replaces all occurrences of the substring from_str within string str with to_str.
33	REVERSE(str) Reverses the order of characters in the string str.
34	RIGHT(str, len) Returns the rightmost len characters from the string str.
35	RPAD(str, len, padstr) Right-pads the string str with the string padstr until str is len characters long.
36	RTRIM(str) Removes trailing whitespace characters from the string str.
37	SOUNDEX(str) Returns a phonetic representation of str as a soundex string. Useful for comparing strings that sound similar but are spelled differently.
38	SPACE(N) Returns a string consisting of N space characters.
39	STRCMP(expr1, expr2) Compares two strings. Returns 0 if they are the same, -1 if the first argument is smaller than the second, and 1 otherwise.
40	SUBSTR(str, pos[, len]) A synonym for SUBSTRING().
41	SUBSTRING(str, pos[, len]) Returns a substring of len characters from string str, starting at position pos. If len is omitted, it returns the rest of the string.
42	SUBSTRING_INDEX(str, delim, count) Returns a substring from str before count occurrences of the delimiter delim. If count is positive, it returns the final substring from the left. If negative, from the right.
43	TO_BASE64(str) Encodes a string to its base-64 representation.
44	TRIM([BOTH | LEADING | TRAILING] [remstr] FROM str) Removes leading and/or trailing characters from a string. By default, it removes whitespace from both ends. A more powerful version of LTRIM and RTRIM.
45	UCASE(str) A synonym for UPPER().
46	UNHEX(str) Performs the inverse of HEX(). Interprets each pair of hexadecimal digits in the argument as a number and converts it to the character it represents.
47	UPPER(str) Returns the string str with all characters changed to uppercase according to the current character set mapping.
48	WEIGHT_STRING(str) Returns the weight string for the input string. This represents the comparison and sorting value of the string.
49	MATCH (col1, col2,…) AGAINST (expr) [Operator] Used for full-text searching. Must be used on columns with a FULLTEXT index.
50	REGEXP [Operator] A synonym for the REGEXP_LIKE() function. Used to check if a string matches a regular expression pattern.
51	SOUNDS LIKE [Operator] Compares the soundex values of two strings. expr1 SOUNDS LIKE expr2 is equivalent to SOUNDEX(expr1) = SOUNDEX(expr2).