| name | mariadb-string-functions |
| description | MariaDB string functions — extraction (SUBSTRING/SUBSTR, LEFT, RIGHT, MID, SUBSTRING_INDEX); length in bytes (LENGTH/OCTET_LENGTH/LENGTHB) vs characters (CHAR_LENGTH/CHARACTER_LENGTH); search (LOCATE, INSTR, POSITION, FIND_IN_SET, FIELD); concatenation (CONCAT, CONCAT_WS); modification (REPLACE, INSERT, REVERSE, REPEAT, SPACE, LPAD/RPAD, TRIM/LTRIM/RTRIM); case (LOWER/UPPER); PCRE2 regular expressions (REGEXP/RLIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR); formatting (FORMAT, SFORMAT); encoding (HEX/UNHEX, TO_BASE64/FROM_BASE64, ASCII, CHAR, ORD); plus the LIKE, REGEXP, SOUNDS LIKE, and BINARY operators. Use when writing SQL that searches, slices, pads, concatenates, case-folds, or pattern-matches string data in MariaDB. |
MariaDB String Functions
Last updated: 2026-06-24
Catalog of every built-in string function in MariaDB, with signature and a one-line semantic summary per entry. For a function not listed here, fall back to the canonical reference at https://mariadb.com/docs/server/reference/sql-functions/string-functions (PCRE2 regex syntax: https://mariadb.com/docs/server/reference/sql-functions/string-functions/regular-expressions-functions).
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Functions with a *(since X.Y)* annotation are only available from that version onward; everything else is in every current LTS branch (10.6, 10.11, 11.4, 11.8).
What LLMs Often Miss
| If the agent writes / assumes… | …prefer the MariaDB form |
|---|
LENGTH(str) to count characters | LENGTH() returns bytes, not characters. Under the default utf8mb4, a 5-emoji string returns 20, not 5. Use CHAR_LENGTH() / CHARACTER_LENGTH() for a character count; OCTET_LENGTH() / LENGTHB() are the explicit byte forms |
CONCAT(a, b, c) and expecting NULL arguments to be skipped | CONCAT() returns NULL if any argument is NULL — one NULL wipes the whole result. Use CONCAT_WS(sep, …) (skips NULLs), or wrap args in IFNULL(x, '') |
SUBSTRING(str, 0, n) to take from the start | String positions are 1-indexed; pos = 0 returns an empty string. Use SUBSTRING(str, 1, n). A negative pos counts from the end (SUBSTRING(str, -4) → last 4 chars) |
LOCATE(str, substr) / INSTR(substr, str) — guessing the argument order | The two take reversed orders: LOCATE(substr, str) (needle first) but INSTR(str, substr) (haystack first). POSITION(substr IN str) is a synonym for LOCATE. All are 1-based, 0 if not found |
str REGEXP '…' expecting POSIX ERE semantics | MariaDB's REGEXP/RLIKE and the REGEXP_* functions use PCRE2 — inline flags (?i)/(?m), lazy quantifiers .+?, backreferences. Treat the dialect as PCRE, not classic POSIX ERE |
Case-sensitivity of LIKE, REGEXP, = is fixed | It depends on the operand collation. With the default utf8mb4_uca1400_ai_ci they are case-insensitive. Force case-sensitive matching with a _bin/_cs collation (COLLATE utf8mb4_bin), the BINARY operator, or a PCRE (?-i) flag. (REPLACE() is always case-sensitive regardless of collation) |
REGEXP_REPLACE(…) with $1-style replacement backreferences | Replacement backreferences use \N (\\1…\\9 in a SQL string literal), not $1. Inline flags like (?i) go inside the pattern |
TRIM(str) to strip an arbitrary character | Bare TRIM removes spaces from both ends. Use `TRIM(LEADING |
| `a | |
LPAD(str, len) / RPAD(str, len) only lengthens | If str is longer than len, the result is truncated to len. len counts characters; the pad string defaults to a single space |
'a' = 'a ' is false (trailing spaces significant) | CHAR/VARCHAR comparison ignores trailing spaces by default, so 'a' = 'a ' is true. Use the BINARY operator (or a NO PAD collation) to make trailing spaces and case significant |
printf-style %s formatting in SQL | Use SFORMAT(fmt, …) for Python/fmtlib-style {} placeholders (since 10.7). Note it has no native temporal/decimal handling (TIME→string, DECIMAL→real) |
Related operators
Pattern-matching and comparison are operators, not functions, so they aren't in the catalog below:
LIKE / NOT LIKE — wildcard match (%, _) with an optional ESCAPE clause; collation-dependent case-sensitivity; index-usable when the pattern doesn't start with %/_.REGEXP / RLIKE / NOT REGEXP — PCRE2 pattern match returning 1/0/NULL; session default_regex_flags sets default modifiers.BINARY — forces a byte-wise (case- and trailing-space-sensitive) comparison; the canonical way to make a comparison case-sensitive.SOUNDS LIKE — shorthand for SOUNDEX(a) = SOUNDEX(b).
To join multiple rows into one string, see GROUP_CONCAT in mariadb-aggregate-functions.
Functions
Auto-generated from the canonical server/reference/sql-functions/string-functions/ pages by agent-skills/extractor/extract_function_category.py; regenerate when the doc tree changes. Do not hand-edit between the markers.
ASCII
ASCII(str)
Returns the numeric ASCII value of the leftmost character of the string argument.
BIN
BIN(N)
Returns a string representation of the binary value of the given longlong (that is, BIGINT) number.
BIT_LENGTH
BIT_LENGTH(str)
Returns the length of the given string argument in bits.
CAST
CAST(expr AS type)
The CAST() function takes a value of one data type and produces a value of another data type, similar to the CONVERT() function.
CHAR
CHAR(N,... [USING charset_name])
CHAR() interprets each argument as an INT and returns a string consisting of the characters given by the code values of those integers.
CHARACTER_LENGTH
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
CHAR_LENGTH
CHAR_LENGTH(str)
Returns the length of the given string argument, measured in characters.
CHR
CHR(N)
CHR() interprets each argument N as an integer and returns a VARCHAR(1) string consisting of the character given by the code values of the integer.
CONCAT
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments.
CONCAT_WS
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT().
CONVERT
CONVERT(expr,type), CONVERT(expr USING transcoding_name)
The CONVERT() and CAST() functions take a value of one type and produce a value of another type.
ELT
ELT(N, str1[, str2, str3,...])
Takes a numeric argument and a series of string arguments.
EXPORT_SET
EXPORT_SET(bits, on, off[, separator[, number_of_bits]])
Takes a minimum of three arguments.
EXTRACTVALUE
EXTRACTVALUE(xml_frag, xpath_expr)
The EXTRACTVALUE() function takes two string arguments: a fragment of XML markup and an XPath expression, (also known as a locator).
FIELD
FIELD(pattern, str1[,str2,...])
Returns the index position of the string or number matching the given pattern.
FIND_IN_SET
FIND_IN_SET(pattern, strlist)
Returns the index position where the given pattern occurs in a string list.
FORMAT
FORMAT(num, decimal_position[, locale])
Formats the given number for display as a string, adding separators to appropriate position and rounding the results to the given decimal position.
FROM_BASE64
FROM_BASE64(str)
Decodes the given base-64 encode string, returning the result as a binary string.
HEX
HEX(N_or_S)
If N_or_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number.
INSERT
INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.
INSTR
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str.
LCASE
LCASE(str)
LCASE() is a synonym for LOWER().
LEFT
LEFT(str,len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.
LENGTH
LENGTH(str)
Returns the length of the string str.
LENGTHB
LENGTHB(str)
LENGTHB() returns the length of the given string, in bytes.
LIKE
expr LIKE pat [ESCAPE 'escape_char']
Tests whether expr matches the pattern pat.
LOAD_FILE
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string.
LOCATE
LOCATE(substr,str), LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of substring substr in string str.
LOWER
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping.
LPAD
LPAD(str, len [,padstr])
Returns the string str, left-padded with the string padstr to a length of len characters.
LTRIM
LTRIM(str)
Returns the string str with leading space characters removed.
MAKE_SET
MAKE_SET(bits,str1,str2,...)
Returns a set value (a string containing substrings separated by "," characters) consisting of the strings that have the corresponding bit in bits set.
MID
MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len)!
NATURAL_SORT_KEY
NATURAL_SORT_KEY(str)
The NATURAL_SORT_KEY function is used for sorting that is closer to natural sorting. (since 10.7)
OCTET_LENGTH
OCTET_LENGTH(str)
OCTET_LENGTH() returns the length of the given string, in octets (bytes).
ORD
ORD(str)
If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:
POSITION
POSITION(substr IN str)
POSITION(substr IN str) is a synonym for LOCATE(substr,str).
QUOTE
QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement.
REPEAT
REPEAT(str,count)
Returns a string consisting of the string str repeated count times.
REPLACE
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str.
REVERSE
REVERSE(str)
Returns the string str with the order of the characters reversed.
RIGHT
RIGHT(str,len)
Returns the rightmost len characters from the string str, or NULL if any argument is NULL.
RPAD
RPAD(str, len [, padstr])
Returns the string str, right-padded with the string padstr to a length of len characters.
RTRIM
RTRIM(str)
Returns the string str with trailing space characters removed.
SOUNDEX
SOUNDEX(str)
Returns a soundex string from str.
SPACE
SPACE(N)
Returns a string consisting of N space characters.
STRCMP
STRCMP(expr1,expr2)
STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 if the strings are otherwise not the same.
SUBSTR
Alias for SUBSTRING.
SUBSTRING
SUBSTRING(str,pos),
The forms without a len argument return a substring from string str starting at position pos.
SUBSTRING_INDEX
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the delimiter delim.
TO_BASE64
TO_BASE64(str)
Converts the string argument str to its base-64 encoded form, returning the result as a character string in the connection character set and collation.
TO_CHAR
TO_CHAR(expr[, fmt])
{% tabs %} {% tab title="Current" %} The TO_CHAR function converts an expr of type date, datetime, time or timestamp to a string.
TRIM
TRIM_ORACLE([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
Returns the string str with all remstr prefixes or suffixes removed.
UCASE
UCASE(str)
UCASE() is a synonym for UPPER().
UNHEX
UNHEX(str)
Performs the inverse operation of HEX(str).
UPDATEXML
UpdateXML(xml_target, xpath_expr, new_xml)
This function replaces a single portion of a given fragment of XML markupxml_target with a new XML fragment new_xml, and then returns the changed XML.
UPPER
UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping.
WEIGHT_STRING
WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [LEVEL levels] [flags])
Returns a binary string representing the string's sorting and comparison value.
See Also
mariadb-features (topical) — the latin1 → utf8mb4 default-charset (since 11.6) and uca1400_ai_ci default-collation (since 11.5) changes that drive every case-sensitivity rowmysql-to-mariadb (topical) — the ||-as-OR default and collation-alias behaviormariadb-aggregate-functions — GROUP_CONCAT for joining rows into a string- Canonical reference on
mariadb.com/docs: https://mariadb.com/docs/server/reference/sql-functions/string-functions
