MySQL String Functions

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 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 UCASE() (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.