{"id":16549,"date":"2021-06-23T23:15:27","date_gmt":"2021-06-23T13:15:27","guid":{"rendered":"https:\/\/database.guide\/?p=16549"},"modified":"2021-06-23T23:15:27","modified_gmt":"2021-06-23T13:15:27","slug":"mariadb-string-functions-full-list","status":"publish","type":"post","link":"https:\/\/database.guide\/mariadb-string-functions-full-list\/","title":{"rendered":"MariaDB String Functions (Full List)"},"content":{"rendered":"\n

This article contains a full list of built-in MariaDB string functions and operators.<\/p>\n\n\n\n\n\n\n\n

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.<\/p>\n\n\n\n

Function<\/th>Description<\/th><\/tr><\/thead>
ASCII()<\/a><\/code><\/td>Returns the numeric ASCII code of the leftmost character of a given string.<\/td><\/tr>
BIN()<\/a><\/code><\/td>Returns a string containing the binary representation of a number.<\/td><\/tr>
BINARY<\/a><\/code><\/td>Casts the string following it to a binary string.<\/td><\/tr>
BIT_LENGTH()<\/a><\/code><\/td>Returns the number of bits in a string.<\/td><\/tr>
CAST()<\/a><\/code><\/td>Casts a value of one type to another type.<\/td><\/tr>
CHAR()<\/a><\/code><\/td>Returns string based on the integer values for the individual characters.<\/td><\/tr>
CHAR_LENGTH()<\/a><\/code><\/td>Returns the length of a string, measured in characters.<\/td><\/tr>
CHARACTER_LENGTH()<\/a><\/code><\/td>Synonym for CHAR_LENGTH()<\/code>.<\/td><\/tr>
CHR()<\/a><\/code><\/td>Returns a character based on the code values provided as an argument.<\/td><\/tr>
CONCAT()<\/a><\/code><\/td>Enables you to concatenate two or more strings.<\/td><\/tr>
CONCAT_WS()<\/a><\/code><\/td>Allows you to add a separator while concatenating one or more strings.<\/td><\/tr>
CONVERT()<\/a><\/code><\/td>Converts a value to another data type. <\/td><\/tr>
ELT()<\/a><\/code><\/td>Returns a list item at a given position in the list.<\/td><\/tr>
EXPORT_SET()<\/a><\/code><\/td>Returns an on string for every bit set, an off string for every bit not set.<\/td><\/tr>
EXTRACTVALUE()<\/a><\/code><\/td>Returns CDATA from an XML fragment.<\/td><\/tr>
FIELD()<\/a><\/code><\/td>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.<\/td><\/tr>
FIND_IN_SET()<\/a><\/code><\/td>Returns the index of a given list item within a string list (for example \u2018item1, item2, item3,\u2026\u2019).<\/td><\/tr>
FORMAT()<\/a><\/code><\/td>Allows you to format a number to a specific format.<\/td><\/tr>
FROM_BASE64()<\/a><\/code><\/td>Decodes a base-64 encoded string and returns the result.<\/td><\/tr>
HEX()<\/a><\/code><\/td>Converts a string to its hexadecimal representation.<\/td><\/tr>
INSERT()<\/a><\/code><\/td>Inserts a string into another string.<\/td><\/tr>
INSTR()<\/a><\/code><\/td>Returns the position of the first occurrence of a substring within a string.<\/td><\/tr>
LCASE()<\/a><\/code><\/td>Synonym for LOWER()<\/code> (converts a string to lowercase).<\/td><\/tr>
LEFT()<\/a><\/code><\/td>Returns the leftmost characters from a string.<\/td><\/tr>
LENGTH()<\/a><\/code><\/td>Returns the length of a string, measured in bytes. Behaves differently, depending on whether it’s in Oracle mode or not. <\/td><\/tr>
LENGTHB()<\/a><\/code><\/td>Returns the length of a string, measured in bytes. Behaves consistently regardless of whether it’s in Oracle mode or not.<\/td><\/tr>
LIKE<\/a><\/code><\/td>Performs pattern matching using an SQL pattern.<\/td><\/tr>
LOAD_FILE()<\/a><\/code><\/td>Reads a file and returns its contents as a string.<\/td><\/tr>
LOCATE()<\/a><\/code><\/td>Returns the position of a substring within a string.<\/td><\/tr>
LOWER()<\/a><\/code><\/td>Converts a string to lowercase.<\/td><\/tr>
LPAD()<\/a><\/code><\/td>Allows you to pad the left part of a string with one or more characters.<\/td><\/tr>
LTRIM()<\/a><\/code><\/td>Trims whitespace from the beginning of a string.<\/td><\/tr>
MAKE_SET()<\/a><\/code><\/td>Returns a set value (a string containing substrings separated by ,<\/code> characters) consisting of the characters specified as arguments when you call the function.<\/td><\/tr>
MATCH AGAINST<\/a><\/code><\/td>Performs a full-text search.<\/td><\/tr>
MID()<\/a><\/code><\/td>Returns a substring starting from the specified position (synonym for SUBSTRING()<\/code>).<\/td><\/tr>
NOT LIKE<\/a><\/code><\/td>Negation of the LIKE<\/code> operator.<\/td><\/tr>
NOT REGEXP<\/a><\/code><\/td>Negation of REGEXP<\/code>.<\/td><\/tr>
NOT RLIKE<\/a><\/code><\/td>Negation of RLIKE<\/code>.<\/td><\/tr>
OCTET_LENGTH()<\/a><\/code><\/td>Returns the length of a string, measured in bytes (synonym of LENGTH()<\/code>).<\/td><\/tr>
ORD()<\/a><\/code><\/td>Returns the numeric value of the leftmost character of a given string.<\/td><\/tr>
POSITION()<\/a><\/code><\/td>Returns the position of the first occurrence of a substring within the string (synonym for LOCATE()<\/code>, but with a slightly different syntax).<\/td><\/tr>
QUOTE()<\/a><\/code><\/td>Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement.<\/td><\/tr>
REGEXP<\/a><\/code><\/td>Determines whether or not a string matches a regular expression.<\/td><\/tr>
REGEXP_INSTR()<\/a><\/code><\/td>Returns the starting index of a substring that matches the regular expression pattern.<\/td><\/tr>
REGEXP_LIKE()<\/code><\/td>Once implemented, this function will determine whether or not a string matches a regular expression (same as REGEXP<\/code>).

As of this writing, REGEXP_LIKE()<\/code> is has been requested, but not added to MariaDB. See this JIRA request<\/a> for its latest status.<\/td><\/tr>
REGEXP_REPLACE()<\/a><\/code><\/td>Replaces occurrences of the substring within a string that matches the given regular expression pattern.<\/td><\/tr>
REGEXP_SUBSTR()<\/a><\/code><\/td>Returns the substring that matches the given regular expression pattern.<\/td><\/tr>
REPEAT()<\/a><\/code><\/td>Used to repeat a string as many times as required.<\/td><\/tr>
REPLACE()<\/a><\/code><\/td>Replaces all occurrences of a substring with another string.<\/td><\/tr>
REVERSE()<\/a><\/code><\/td>Returns a string with the order of the characters reversed.<\/td><\/tr>
RIGHT()<\/a><\/code><\/td>Returns the rightmost characters from a string.<\/td><\/tr>
RLIKE<\/a><\/code><\/td>Determines whether or not a string matches a regular expression.<\/td><\/tr>
RPAD()<\/a><\/code><\/td>Allows you to pad the right part of a string with one or more characters.<\/td><\/tr>
RTRIM()<\/a><\/code><\/td>Trims whitespace from the right-hand side of a string.<\/td><\/tr>
SOUNDEX()<\/a><\/code><\/td>Returns a Soundex string from a given string.<\/td><\/tr>
SOUNDS LIKE<\/a><\/code><\/td>Compares sounds.<\/td><\/tr>
SPACE()<\/a><\/code><\/td>Returns a string consisting of a specified number of space characters.<\/td><\/tr>
STRCMP()<\/a><\/code><\/td>Compares two strings according to their sort order.<\/td><\/tr>
SUBSTR()<\/a><\/code><\/td>Returns a substring starting from the specified position (synonym for SUBSTRING()<\/code>).<\/td><\/tr>
SUBSTRING()<\/a><\/code><\/td>Returns a substring starting from the specified position.<\/td><\/tr>
SUBSTRING_INDEX()<\/a><\/code><\/td>Returns everything before or after a certain character (or characters) in a string.<\/td><\/tr>
TO_BASE64()<\/a><\/code><\/td>Converts a string to a base-64 encoded string and returns the result.<\/td><\/tr>
TO_CHAR()<\/a><\/code><\/td>Converts a date\/time\/timestamp expression to a string.<\/td><\/tr>
TRIM()<\/a><\/code><\/td>Trims whitespace (or other specified characters) from the start and\/or end of a string.<\/td><\/tr>
TRIM_ORACLE()<\/a><\/code><\/td>Synonym for the Oracle mode version of the TRIM()<\/code> function.<\/td><\/tr>
UCASE()<\/a><\/code><\/td>Synonym for UPPER()<\/code> (converts a string to uppercase).<\/td><\/tr>
UNCOMPRESSED_LENGTH()<\/a><\/code><\/td>Returns the length of a compressed string before it was compressed with the COMPRESS()<\/code> function.<\/td><\/tr>
UNHEX()<\/a><\/code><\/td>Interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number.<\/td><\/tr>
UPDATEXML()<\/a><\/code><\/td>Replaces a single portion of a given fragment of XML markup with a new XML fragment.<\/td><\/tr>
UPPER()<\/a><\/code><\/td>Converts a string to uppercase.<\/td><\/tr>
WEIGHT_STRING()<\/a><\/code><\/td>Returns the weight string for the input string. This function is a debugging function intended for internal use.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

This article contains a full list of built-in MariaDB string functions and operators.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[117],"tags":[101,67],"class_list":["post-16549","post","type-post","status-publish","format-standard","hentry","category-mariadb","tag-reference","tag-string-functions"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/16549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=16549"}],"version-history":[{"count":33,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/16549\/revisions"}],"predecessor-version":[{"id":17847,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/16549\/revisions\/17847"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=16549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=16549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=16549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}