MySQL includes a bunch of functions and operators that can help us when working with data.
The following is a list of string functions and operators available in MySQL. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
ASCII() |
Returns the numeric ASCII code of the leftmost character of a given string. |
BIN() |
Returns a string containing binary representation of a number. |
BIT_LENGTH() |
Returns the number of bits in a string. |
CHAR() |
Returns the character for each integer passed. |
CHAR_LENGTH() |
Returns the length of a string, measured in characters. |
CHARACTER_LENGTH() |
Synonym for CHAR_LENGTH(). |
CONCAT() |
Enables you to concatenate two or more strings. |
CONCAT_WS() |
Allows you to add a separator while concatenating one or more strings. |
ELT() |
Returns a list item at a given position in the list. |
EXPORT_SET() |
Returns a string that represents the bits in a number. |
FIELD() |
Returns the position of a given string or number within a list of arguments. The function returns the index (position) of the first argument in the list of subsequent arguments. |
FIND_IN_SET() |
Returns the index of a given list item within a string list (for example ‘item1, item2, item3,…’). |
FORMAT() |
Allows you to format a number to a specific format. |
FROM_BASE64() |
Decodes a base-64 encoded string and returns the result. |
HEX() |
Converts a string to its hexadecimal representation. |
INSERT() |
Inserts a string into another string. |
INSTR() |
Returns the position of the first occurrence of a substring within a string. |
LCASE() |
Synonym for LOWER() (converts a string to lowercase). |
LEFT() |
Returns the leftmost characters from a string. |
LENGTH() |
Returns the length of a string, measured in bytes. |
LIKE |
Performs pattern matching using an SQL pattern. |
LOAD_FILE() |
Reads a file and returns its contents as a string. |
LOCATE() |
Returns the position of a substring within a string. |
LOWER() |
Converts a string to lowercase. |
LPAD() |
Allows you to pad the left part of a string with one or more characters. |
LTRIM() |
Trims whitespace from the beginning of a string. |
MAKE_SET() |
Returns a set value (a string containing substrings separated by , characters) consisting of the characters specified as arguments when you call the function. |
MATCH() |
Performs a full-text search. |
MID() |
Returns a substring starting from the specified position (synonym for SUBSTRING()). |
NOT LIKE |
Negation of the LIKE operator. |
NOT REGEXP |
Negation of REGEXP. |
NOT RLIKE |
Negation of RLIKE. |
OCT() |
Returns a string representation of the octal value of its argument (converts from decimal to octal). |
OCTET_LENGTH() |
Returns the length of a string, measured in bytes (synonym of LENGTH()). |
ORD() |
Returns the numeric value of the leftmost character of a given string. |
POSITION() |
Returns the position of the first occurrence of a substring within the string (synonym for LOCATE(), but with a slightly different syntax). |
QUOTE() |
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. |
REGEXP |
Determines whether or not a string matches a regular expression (synonym for REGEXP_LIKE()). |
REGEXP_INSTR() |
Returns the starting index of a substring that matches the regular expression pattern. |
REGEXP_LIKE() |
Determines whether or not a string matches a regular expression. |
REGEXP_REPLACE() |
Replaces occurrences of the substring within a string that matches the given regular expression pattern. |
REGEX_SUBSTR() |
Returns the substring that matches the given regular expression pattern. |
REPEAT() |
Used to repeat a string as many times as required. |
REPLACE() |
Replaces all occurrences of a substring with another string. |
REVERSE() |
Returns a string with the order of the characters reversed. |
RIGHT() |
Returns the rightmost characters from a string. |
RLIKE |
Determines whether or not a string matches a regular expression (synonym for REGEXP_LIKE()). |
RPAD() |
Allows you to pad the right part of a string with one or more characters. |
RTRIM() |
Trims whitespace from the right-hand side of a string. |
SOUNDEX() |
Returns a Soundex string from a given string. |
SOUNDS LIKE |
Compares sounds. |
SPACE() |
Returns a string consisting of a specified number of space characters. |
STRCMP() |
Compares two strings according to their sort order. |
SUBSTR() |
Returns a substring starting from the specified position (synonym for SUBSTRING()). |
SUBSTRING() |
Returns a substring starting from the specified position. |
SUBSTRING_INDEX() |
Returns everything before or after a certain character (or characters) in a string. |
TO_BASE64() |
Converts a string to a base-64 encoded string and returns the result. |
TRIM() |
Trims whitespace (or other specified characters) from the start and/or end of a string. |
UCASE() |
Synonym for UPPER() (converts a string to uppercase). |
UNHEX() |
Interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. |
UPPER() |
Converts a string to uppercase. |
WEIGHT_STRING() |
Returns the weight string for the input string. This function is a debugging function intended for internal use. |