MariaDB String Functions (Full List)

This article contains a full list of built-in MariaDB string functions and operators.

The following is a list of string functions and operators available in MariaDB. Click on each function or operator name to see an explanation of the function, its syntax, and examples.

Function	Description
`ASCII()`	Returns the numeric ASCII code of the leftmost character of a given string.
`BIN()`	Returns a string containing the binary representation of a number.
`BINARY`	Casts the string following it to a binary string.
`BIT_LENGTH()`	Returns the number of bits in a string.
`CAST()`	Casts a value of one type to another type.
`CHAR()`	Returns string based on the integer values for the individual characters.
`CHAR_LENGTH()`	Returns the length of a string, measured in characters.
`CHARACTER_LENGTH()`	Synonym for `CHAR_LENGTH()`.
`CHR()`	Returns a character based on the code values provided as an argument.
`CONCAT()`	Enables you to concatenate two or more strings.
`CONCAT_WS()`	Allows you to add a separator while concatenating one or more strings.
`CONVERT()`	Converts a value to another data type.
`ELT()`	Returns a list item at a given position in the list.
`EXPORT_SET()`	Returns an on string for every bit set, an off string for every bit not set.
`EXTRACTVALUE()`	Returns CDATA from an XML fragment.
`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. Behaves differently, depending on whether it’s in Oracle mode or not.
`LENGTHB()`	Returns the length of a string, measured in bytes. Behaves consistently regardless of whether it’s in Oracle mode or not.
`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 AGAINST`	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`.
`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.
`REGEXP_INSTR()`	Returns the starting index of a substring that matches the regular expression pattern.
`REGEXP_LIKE()`	Once implemented, this function will determine whether or not a string matches a regular expression (same as `REGEXP`). As of this writing, `REGEXP_LIKE()` is has been requested, but not added to MariaDB. See this JIRA request for its latest status.
`REGEXP_REPLACE()`	Replaces occurrences of the substring within a string that matches the given regular expression pattern.
`REGEXP_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.
`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.
`TO_CHAR()`	Converts a date/time/timestamp expression to a string.
`TRIM()`	Trims whitespace (or other specified characters) from the start and/or end of a string.
`TRIM_ORACLE()`	Synonym for the Oracle mode version of the `TRIM()` function.
`UCASE()`	Synonym for `UPPER()` (converts a string to uppercase).
`UNCOMPRESSED_LENGTH()`	Returns the length of a compressed string before it was compressed with the `COMPRESS()` function.
`UNHEX()`	Interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number.
`UPDATEXML()`	Replaces a single portion of a given fragment of XML markup with a new XML fragment.
`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.