🔤

MariaDB String Functions

MariaDB

Complete reference for MariaDB string functions covering CONCAT, SUBSTRING, REPLACE, TRIM, REGEXP_REPLACE, LPAD, LOCATE, and 60+ more. Every function includes syntax, real SQL examples, and differences from MySQL and PostgreSQL string functions. Updated for MariaDB 11.x.

62 functions

What are MariaDB String Functions?

MariaDB string functions are built-in functions for text manipulation in MariaDB queries. MariaDB string functions are largely compatible with MySQL but include additional functions like REGEXP_REPLACE() (available natively since MariaDB 10.0.5, before MySQL added it) and NATURAL_SORT_KEY(). A key difference from PostgreSQL is that string concatenation uses CONCAT() rather than ||, and MariaDB string comparison is case-insensitive by default due to collation settings.

CONCAT

→ VARCHAR / TEXT

Concatenates one or more strings into a single string, returning NULL if any argument is NULL.

Signature

CONCAT(str1, str2, ...)

Parameters

Parameter	Type	Description
str1, str2, ...	VARCHAR	Two or more strings to concatenate

Examples

Basic concatenation

sql

SELECT CONCAT('Hello', ' ', 'World');

→-- 'Hello World'

Joining version string

sql

SELECT CONCAT('MariaDB', ' ', '10.6');

→-- 'MariaDB 10.6'

Combining columns

sql

SELECT CONCAT(first_name, ' ', last_name) FROM users;

→-- 'Jane Doe'

Nested calls

sql

SELECT CONCAT(UPPER('hello'), LOWER('WORLD'));

→-- 'HELLOworld'

Any NULL argument yields NULL

sql

SELECT CONCAT('a', NULL, 'b');

→-- NULL

⚠Anti-Pattern— Forgetting NULL propagation

CONCAT returns NULL if any single argument is NULL, silently wiping the whole result.

✓ Instead: Wrap nullable columns with COALESCE: CONCAT(COALESCE(col, ''), ' ', other_col).

◆MariaDB Note

In STRICT mode, non-string arguments are implicitly cast; outside strict mode numeric types are silently coerced.

⇄ vs MySQL: Identical to MySQL. Both return NULL on any NULL argument.

⇄ vs PostgreSQL: PostgreSQL also has CONCAT() but its idiomatic style is the || operator; PG's CONCAT ignores NULLs whereas MariaDB's does not.

CONCAT_WS

→ VARCHAR / TEXT

Concatenates strings with a separator, skipping NULL values (but returning NULL if the separator itself is NULL).

Signature

CONCAT_WS(separator, str1, str2, ...)

Parameters

Parameter	Type	Description
separator	VARCHAR	String placed between each non-NULL value
str1, str2, ...	VARCHAR	Values to join

Examples

Comma-separated list

sql

SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Carol');

→-- 'Alice, Bob, Carol'

Building a date string

sql

SELECT CONCAT_WS('-', '2026', '03', '09');

→-- '2026-03-09'

NULL values are skipped

sql

SELECT CONCAT_WS(' ', first_name, middle_name, last_name) FROM users;

→-- 'Jane Doe' (middle_name NULL skipped)

Nested function calls

sql

SELECT CONCAT_WS('|', UPPER('a'), LOWER('B'));

→-- 'A|b'

NULL separator returns NULL

sql

SELECT CONCAT_WS(NULL, 'a', 'b');

→-- NULL

⚠Anti-Pattern— Using CONCAT when separator is constant

Writing CONCAT(col1, ', ', col2, ', ', col3) becomes unwieldy and breaks when any column is NULL.

✓ Instead: Use CONCAT_WS(', ', col1, col2, col3) — cleaner and NULL-safe for the value arguments.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has CONCAT_WS with the same NULL-skipping semantics.

LENGTH

→ INT

Returns the byte length of a string, which differs from character count for multi-byte character sets.

Signature

LENGTH(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The string whose byte length is measured

Examples

ASCII string — bytes equal chars

sql

SELECT LENGTH('Hello');

→-- 5

UTF-8: 'é' is 2 bytes

sql

SELECT LENGTH('café');

→-- 5

On a column

sql

SELECT LENGTH(col) FROM products;

→-- byte length per row

Nested

sql

SELECT LENGTH(CONCAT('ab', 'cd'));

→-- 4

NULL input returns NULL

sql

SELECT LENGTH(NULL);

→-- NULL

⚠Anti-Pattern— Validating user-input length with LENGTH()

A 10-character Chinese string may return LENGTH() = 30 under utf8mb4, causing false 'too long' rejections.

✓ Instead: Use CHAR_LENGTH() for character-count validation.

◆MariaDB Note

Synonym for OCTET_LENGTH().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL's LENGTH() returns character count by default; use OCTET_LENGTH() for bytes.

CHAR_LENGTH

→ INT

Returns the number of characters in a string, regardless of multi-byte encoding.

Signatures

CHAR_LENGTH(str)

CHARACTER_LENGTH(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The string to measure in characters

Examples

ASCII

sql

SELECT CHAR_LENGTH('Hello');

→-- 5

4 characters despite 5 bytes in UTF-8

sql

SELECT CHAR_LENGTH('café');

→-- 4

On a column

sql

SELECT CHAR_LENGTH(name) FROM customers;

→-- character count per row

Nested

sql

SELECT CHAR_LENGTH(REVERSE('abc'));

→-- 3

NULL handling

sql

SELECT CHAR_LENGTH(NULL);

→-- NULL

⚠Anti-Pattern— Mixing CHAR_LENGTH and LENGTH for the same check

Using both interchangeably in the same query creates confusing inconsistencies for multi-byte data.

✓ Instead: Pick CHAR_LENGTH for display/validation purposes and LENGTH only when you truly need byte sizes.

◆MariaDB Note

CHARACTER_LENGTH() is a synonym.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL's CHAR_LENGTH() and CHARACTER_LENGTH() behave identically.

CHARACTER_LENGTH

→ INT

Synonym for CHAR_LENGTH(); returns the number of characters in a string.

Signature

CHARACTER_LENGTH(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The string to measure

Examples

Basic usage

sql

SELECT CHARACTER_LENGTH('test');

→-- 4

3 CJK characters

sql

SELECT CHARACTER_LENGTH('日本語');

→-- 3

On a column

sql

SELECT CHARACTER_LENGTH(bio) FROM authors;

→-- character count

Nested

sql

SELECT CHARACTER_LENGTH(TRIM('  hi  '));

→-- 2

NULL handling

sql

SELECT CHARACTER_LENGTH(NULL);

→-- NULL

⚠Anti-Pattern— Confusing with LENGTH()

Using LENGTH() when you mean CHARACTER_LENGTH() gives wrong results for multi-byte character sets.

✓ Instead: Use CHARACTER_LENGTH() for character count, LENGTH() only for byte count.

◆MariaDB Note

Exact synonym for CHAR_LENGTH().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: Identical behavior in PostgreSQL.

OCTET_LENGTH

→ INT

Returns the byte (octet) length of a string; synonym for LENGTH() in MariaDB.

Signature

OCTET_LENGTH(str)

Parameters

Parameter	Type	Description
str	VARCHAR	The string whose byte count is returned

Examples

ASCII

sql

SELECT OCTET_LENGTH('abc');

→-- 3

3 chars × 3 bytes each in UTF-8

sql

SELECT OCTET_LENGTH('日本語');

→-- 9

On a column

sql

SELECT OCTET_LENGTH(content) FROM pages;

→-- byte size per row

Nested with HEX

sql

SELECT OCTET_LENGTH(HEX('a'));

→-- 2

NULL handling

sql

SELECT OCTET_LENGTH(NULL);

→-- NULL

⚠Anti-Pattern— Treating OCTET_LENGTH as character count

For multi-byte strings, OCTET_LENGTH can be several times CHAR_LENGTH, leading to wrong truncation logic.

✓ Instead: Use CHAR_LENGTH for character-count checks and OCTET_LENGTH only for raw storage/network-size calculations.

◆MariaDB Note

Exact synonym for LENGTH().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL's OCTET_LENGTH() behaves identically.

SUBSTRING

→ VARCHAR / TEXT

Extracts a substring from a string, starting at a given position with an optional length.

Signatures

SUBSTRING(str, pos)

SUBSTRING(str, pos, len)

SUBSTRING(str FROM pos)

SUBSTRING(str FROM pos FOR len)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
pos	INT	1-based starting position; negative counts from the end
len	INT	Number of characters to extract (optional)

Examples

From position 7 to end

sql

SELECT SUBSTRING('Hello World', 7);

→-- 'World'

First 5 chars

sql

SELECT SUBSTRING('Hello World', 1, 5);

→-- 'Hello'

Negative pos counts from end

sql

SELECT SUBSTRING('Hello World', -5);

→-- 'World'

Nested

sql

SELECT SUBSTRING(UPPER('hello'), 2, 3);

→-- 'ELL'

NULL handling

sql

SELECT SUBSTRING(NULL, 1, 3);

→-- NULL

⚠Anti-Pattern— Off-by-one errors from 0-based thinking

MariaDB uses 1-based indexing. SUBSTRING(str, 0, 5) treats position 0 as position 1 and can return unexpected results.

✓ Instead: Always start positions at 1 for the first character.

◆MariaDB Note

SUBSTR() and MID() are exact synonyms.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL supports SUBSTRING(str FROM pos FOR len) SQL-standard syntax and also accepts regex patterns.

SUBSTR

→ VARCHAR / TEXT

Synonym for SUBSTRING(); extracts part of a string by position and optional length.

Signatures

SUBSTR(str, pos)

SUBSTR(str, pos, len)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
pos	INT	1-based start position
len	INT	Length of substring (optional)

Examples

From position 5

sql

SELECT SUBSTR('database', 5);

→-- 'base'

First 4 chars

sql

SELECT SUBSTR('database', 1, 4);

→-- 'data'

On a column

sql

SELECT SUBSTR(tag, 2) FROM labels;

→-- strips first char

Nested

sql

SELECT SUBSTR(CONCAT('ab','cd'), 2, 2);

→-- 'bc'

NULL length yields NULL

sql

SELECT SUBSTR('abc', 1, NULL);

→-- NULL

⚠Anti-Pattern— Assuming 0-based indexing

Coming from Python or JavaScript, developers often pass 0 as the start index, getting an empty or shifted result.

✓ Instead: Use 1 for the first character.

◆MariaDB Note

Exact synonym for SUBSTRING() and MID().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has SUBSTR() as a synonym for SUBSTRING().

MID

→ VARCHAR / TEXT

Synonym for SUBSTRING(); extracts a substring starting at a position for a given length.

Signature

MID(str, pos, len)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
pos	INT	1-based starting position
len	INT	Number of characters to return

Examples

Classic MID usage

sql

SELECT MID('Hello World', 7, 5);

→-- 'World'

Middle slice

sql

SELECT MID('abcdef', 2, 3);

→-- 'bcd'

On a column

sql

SELECT MID(serial, 4, 6) FROM devices;

→-- mid-section of serial

Nested

sql

SELECT MID(REPEAT('ab', 3), 2, 4);

→-- 'baba'

NULL len

sql

SELECT MID('test', 2, NULL);

→-- NULL

⚠Anti-Pattern— Relying on MID in cross-database SQL

MID is not standard SQL and absent in PostgreSQL and SQLite.

✓ Instead: Use SUBSTRING(str, pos, len) for maximum portability.

◆MariaDB Note

Exact synonym for SUBSTRING().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL does not have MID(); use SUBSTRING().

LEFT

→ VARCHAR / TEXT

Returns the leftmost N characters of a string.

Signature

LEFT(str, len)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
len	INT	Number of characters to return from the left

Examples

First 5 chars

sql

SELECT LEFT('Hello World', 5);

→-- 'Hello'

Prefix extraction

sql

SELECT LEFT('MariaDB', 5);

→-- 'Maria'

On a column

sql

SELECT LEFT(phone, 3) FROM contacts;

→-- area code

Nested

sql

SELECT LEFT(UPPER('hello'), 3);

→-- 'HEL'

NULL len

sql

SELECT LEFT('test', NULL);

→-- NULL

⚠Anti-Pattern— Over-truncating without checking length

LEFT('hi', 10) is safe (returns 'hi'), but dynamically computed lengths from user input can still produce unexpected empty strings if len = 0.

✓ Instead: Guard with GREATEST(len, 0) if length is computed.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has LEFT() with identical behavior.

RIGHT

→ VARCHAR / TEXT

Returns the rightmost N characters of a string.

Signature

RIGHT(str, len)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
len	INT	Number of characters to return from the right

Examples

Last 5 chars

sql

SELECT RIGHT('Hello World', 5);

→-- 'World'

File extension

sql

SELECT RIGHT('filename.txt', 3);

→-- 'txt'

On a column

sql

SELECT RIGHT(zip_code, 4) FROM addresses;

→-- ZIP+4 suffix

Nested

sql

SELECT RIGHT(LPAD('42', 6, '0'), 4);

→-- '0042'

NULL handling

sql

SELECT RIGHT(NULL, 3);

→-- NULL

⚠Anti-Pattern— Using RIGHT to check suffix instead of LIKE

WHERE RIGHT(col, 4) = '.pdf' is not index-friendly.

✓ Instead: Use WHERE col LIKE '%.pdf' which can leverage reverse-index tricks or full-text search.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has RIGHT() with identical behavior.

TRIM

→ VARCHAR / TEXT

Removes leading and/or trailing characters (default: spaces) from a string.

Signatures

TRIM(str)

TRIM(LEADING remstr FROM str)

TRIM(TRAILING remstr FROM str)

TRIM(BOTH remstr FROM str)

Parameters

Parameter	Type	Description
str	VARCHAR	The string to trim
remstr	VARCHAR	Character(s) to remove (default is space)

Examples

Removes surrounding spaces

sql

SELECT TRIM('  Hello  ');

→-- 'Hello'

Remove leading zeros

sql

SELECT TRIM(LEADING '0' FROM '00123');

→-- '123'

Remove trailing dots

sql

SELECT TRIM(TRAILING '.' FROM 'end...');

→-- 'end'

Trim both sides

sql

SELECT TRIM(BOTH 'x' FROM 'xxhelloxx');

→-- 'hello'

NULL handling

sql

SELECT TRIM(NULL);

→-- NULL

⚠Anti-Pattern— Expecting TRIM to strip interior spaces

TRIM only removes characters from the edges of the string, not internal whitespace.

✓ Instead: Use REGEXP_REPLACE(str, '\\s+', ' ') to collapse internal whitespace.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL TRIM() supports identical LEADING/TRAILING/BOTH syntax.

LTRIM

→ VARCHAR / TEXT

Removes leading (left-side) spaces from a string.

Signature

LTRIM(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to left-trim

Examples

Strips leading spaces

sql

SELECT LTRIM('   Hello');

→-- 'Hello'

Trailing spaces kept

sql

SELECT LTRIM('   Hello   ');

→-- 'Hello '

On a column

sql

SELECT LTRIM(notes) FROM tickets;

→-- left-trimmed notes

Combining for full trim

sql

SELECT LTRIM(RTRIM('  hi  '));

→-- 'hi'

NULL handling

sql

SELECT LTRIM(NULL);

→-- NULL

⚠Anti-Pattern— Using LTRIM to strip non-space whitespace

LTRIM does not remove \t, \n, or \r characters.

✓ Instead: Use REGEXP_REPLACE(str, '^[[:space:]]+', '') to strip all leading whitespace.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL LTRIM(str, chars) can trim a set of characters; MariaDB's LTRIM only strips spaces.

RTRIM

→ VARCHAR / TEXT

Removes trailing (right-side) spaces from a string.

Signature

RTRIM(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to right-trim

Examples

Strips trailing spaces

sql

SELECT RTRIM('Hello   ');

→-- 'Hello'

Leading spaces kept

sql

SELECT RTRIM('   Hello   ');

→-- ' Hello'

On a column

sql

SELECT RTRIM(description) FROM products;

→-- trailing-trimmed

Combining for full trim

sql

SELECT RTRIM(LTRIM('  hi  '));

→-- 'hi'

NULL handling

sql

SELECT RTRIM(NULL);

→-- NULL

⚠Anti-Pattern— Forgetting that VARCHAR does not pad but CHAR does

CHAR(10) columns store trailing spaces; RTRIM is needed for equality checks on CHAR columns.

✓ Instead: Compare CHAR columns with RTRIM(col) = 'value' or switch to VARCHAR.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL RTRIM(str, chars) also accepts a character set to strip.

LPAD

→ VARCHAR / TEXT

Left-pads a string with a given pad string until it reaches the specified total length.

Signature

LPAD(str, len, padstr)

Parameters

Parameter	Type	Description
str	VARCHAR	String to pad
len	INT	Desired total length of the result
padstr	VARCHAR	String used for padding

Examples

Zero-pad a number

sql

SELECT LPAD('42', 6, '0');

→-- '000042'

Dash padding

sql

SELECT LPAD('hi', 5, '-');

→-- '---hi'

On a column

sql

SELECT LPAD(invoice_num, 8, '0') FROM orders;

→-- zero-padded IDs

Nested

sql

SELECT LPAD(LTRIM('  abc'), 6, '*');

→-- '***abc'

Truncates when str longer than len

sql

SELECT LPAD('toolong', 3, 'x');

→-- 'too'

⚠Anti-Pattern— Padding NULLs without checking

LPAD(NULL, 5, '0') returns NULL, which may break report formatting.

✓ Instead: Use LPAD(COALESCE(col, ''), 5, '0') to treat NULL as an empty string.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL LPAD() is identical.

RPAD

→ VARCHAR / TEXT

Right-pads a string with a given pad string until it reaches the specified total length.

Signature

RPAD(str, len, padstr)

Parameters

Parameter	Type	Description
str	VARCHAR	String to pad
len	INT	Desired total length
padstr	VARCHAR	String used for padding on the right

Examples

Dot padding

sql

SELECT RPAD('hello', 8, '.');

→-- 'hello...'

Right zero-pad

sql

SELECT RPAD('42', 6, '0');

→-- '420000'

On a column

sql

SELECT RPAD(code, 10, '-') FROM items;

→-- fixed-width codes

Nested

sql

SELECT RPAD(UPPER('hi'), 6, '!');

→-- 'HI!!!!'

Truncates if already longer

sql

SELECT RPAD('toolong', 3, 'x');

→-- 'too'

⚠Anti-Pattern— Using a multi-char padstr and assuming it tiles exactly

RPAD('x', 5, 'ab') yields 'xabab' — the pad string is cut if it doesn't divide evenly.

✓ Instead: That is expected behavior; just be aware the pad string repeats and gets truncated at the target length.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL RPAD() is identical.

UPPER

→ VARCHAR / TEXT

Converts a string to uppercase using the current character set collation rules.

Signature

UPPER(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to convert to uppercase

Examples

Basic uppercase

sql

SELECT UPPER('hello');

→-- 'HELLO'

Mixed input

sql

SELECT UPPER('MariaDB 10.6');

→-- 'MARIADB 10.6'

On a column

sql

SELECT UPPER(username) FROM users;

→-- uppercased usernames

Nested

sql

SELECT UPPER(CONCAT('foo', 'bar'));

→-- 'FOOBAR'

NULL handling

sql

SELECT UPPER(NULL);

→-- NULL

⚠Anti-Pattern— Using UPPER() in WHERE to force case-insensitive match

WHERE UPPER(col) = UPPER('value') disables index use on col.

✓ Instead: Set the column collation to a _ci variant and query directly: WHERE col = 'value'.

◆MariaDB Note

UCASE() is a synonym.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL UPPER() behaves identically for Unicode.

LOWER

→ VARCHAR / TEXT

Converts a string to lowercase using the current character set collation rules.

Signature

LOWER(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to convert to lowercase

Examples

Basic lowercase

sql

SELECT LOWER('HELLO');

→-- 'hello'

Mixed case

sql

SELECT LOWER('MariaDB');

→-- 'mariadb'

On a column

sql

SELECT LOWER(email) FROM accounts;

→-- lowercased emails

Nested

sql

SELECT LOWER(CONCAT('FOO', 'BAR'));

→-- 'foobar'

NULL handling

sql

SELECT LOWER(NULL);

→-- NULL

⚠Anti-Pattern— Normalizing case at query time instead of storage time

WHERE LOWER(email) = 'user@example.com' prevents index use.

✓ Instead: Store email as LOWER(email) via a BEFORE INSERT trigger or application logic, then query directly.

◆MariaDB Note

LCASE() is a synonym.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL LOWER() behaves identically.

UCASE

→ VARCHAR / TEXT

Synonym for UPPER(); converts a string to uppercase.

Signature

UCASE(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to uppercase

Examples

Basic

sql

SELECT UCASE('hello');

→-- 'HELLO'

Unicode

sql

SELECT UCASE('café');

→-- 'CAFÉ'

On a column

sql

SELECT UCASE(name) FROM brands;

→-- uppercased brand names

Nested

sql

SELECT UCASE(TRIM('  hi  '));

→-- 'HI'

NULL handling

sql

SELECT UCASE(NULL);

→-- NULL

⚠Anti-Pattern— Mixing UCASE and UPPER in the same codebase

Having both aliases in queries is confusing for maintainers.

✓ Instead: Standardize on UPPER() across all queries.

◆MariaDB Note

Exact synonym for UPPER().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL does not have UCASE(); use UPPER().

LCASE

→ VARCHAR / TEXT

Synonym for LOWER(); converts a string to lowercase.

Signature

LCASE(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to lowercase

Examples

Basic

sql

SELECT LCASE('HELLO');

→-- 'hello'

Mixed

sql

SELECT LCASE('MariaDB');

→-- 'mariadb'

On a column

sql

SELECT LCASE(email) FROM users;

→-- lowercased

Nested

sql

SELECT LCASE(CONCAT('A', 'B'));

→-- 'ab'

NULL handling

sql

SELECT LCASE(NULL);

→-- NULL

⚠Anti-Pattern— Mixing LCASE and LOWER in the same codebase

Inconsistent aliasing makes code harder to read.

✓ Instead: Standardize on LOWER().

◆MariaDB Note

Exact synonym for LOWER().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL does not have LCASE(); use LOWER().

REPLACE

→ VARCHAR / TEXT

Replaces all occurrences of a substring within a string with a new substring.

Signature

REPLACE(str, from_str, to_str)

Parameters

Parameter	Type	Description
str	VARCHAR	The source string
from_str	VARCHAR	Substring to search for
to_str	VARCHAR	Replacement substring

Examples

Basic replacement

sql

SELECT REPLACE('Hello World', 'World', 'MariaDB');

→-- 'Hello MariaDB'

Replaces all occurrences

sql

SELECT REPLACE('aabbcc', 'b', 'X');

→-- 'aaXXcc'

On a column

sql

SELECT REPLACE(body, '<br>', '\n') FROM posts;

→-- newlines instead of br tags

Chained replaces

sql

SELECT REPLACE(REPLACE('a-b-c', '-', '/'), '/', '|');

→-- 'a|b|c'

NULL anywhere yields NULL

sql

SELECT REPLACE('test', NULL, 'x');

→-- NULL

⚠Anti-Pattern— Using REPLACE for pattern-based substitution

REPLACE only does literal string matching — it cannot handle wildcards or patterns.

✓ Instead: Use REGEXP_REPLACE for pattern-based replacement.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL REPLACE() behaves identically.

REVERSE

→ VARCHAR / TEXT

Returns the string with characters in reverse order.

Signature

REVERSE(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to reverse

Examples

Basic reversal

sql

SELECT REVERSE('Hello');

→-- 'olleH'

Alphabet slice

sql

SELECT REVERSE('abcde');

→-- 'edcba'

On a column

sql

SELECT REVERSE(serial) FROM parts;

→-- reversed serials

Nested

sql

SELECT REVERSE(UPPER('hello'));

→-- 'OLLEH'

NULL handling

sql

SELECT REVERSE(NULL);

→-- NULL

⚠Anti-Pattern— Using REVERSE in WHERE for suffix matching

WHERE REVERSE(col) LIKE 'moc.%' disables index use on col.

✓ Instead: Store a reversed_col computed column with an index and query that column directly.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has REVERSE() with identical behavior.

REPEAT

→ VARCHAR / TEXT

Returns a string repeated N times; returns an empty string if count is less than 1.

Signature

REPEAT(str, count)

Parameters

Parameter	Type	Description
str	VARCHAR	String to repeat
count	INT	Number of repetitions

Examples

Three repetitions

sql

SELECT REPEAT('ab', 3);

→-- 'ababab'

Horizontal rule

sql

SELECT REPEAT('-', 20);

→-- '--------------------'

On a column

sql

SELECT REPEAT('*', rating) FROM reviews;

→-- star rating display

Nested

sql

SELECT REPEAT(UPPER('ha'), 3);

→-- 'HAHAHA'

Zero count returns empty string

sql

SELECT REPEAT('x', 0);

→-- ''

⚠Anti-Pattern— Repeating with a large count from user input

REPEAT('x', 1000000) can produce a very large string and exhaust memory.

✓ Instead: Cap count with LEAST(user_count, 255) before passing to REPEAT.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has REPEAT() with identical behavior.

SPACE

→ VARCHAR

Returns a string of N space characters.

Signature

SPACE(n)

Parameters

Parameter	Type	Description
n	INT	Number of space characters to generate

Examples

5 spaces between pipes

sql

SELECT CONCAT('|', SPACE(5), '|');

→-- '| |'

Zero returns empty string

sql

SELECT SPACE(0);

→-- ''

Manual right-pad

sql

SELECT CONCAT(name, SPACE(20 - CHAR_LENGTH(name))) FROM items;

→-- right-padded names

Nested length check

sql

SELECT CHAR_LENGTH(SPACE(10));

→-- 10

NULL handling

sql

SELECT SPACE(NULL);

→-- NULL

⚠Anti-Pattern— Using SPACE for alignment instead of LPAD/RPAD

Manual CONCAT with SPACE for padding is verbose and error-prone.

✓ Instead: Use LPAD or RPAD for fixed-width alignment.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks SPACE(); use REPEAT(' ', n).

LOCATE

→ INT

Returns the position of the first occurrence of a substring in a string, optionally starting the search at a given position.

Signatures

LOCATE(substr, str)

LOCATE(substr, str, pos)

Parameters

Parameter	Type	Description
substr	VARCHAR	Substring to search for
str	VARCHAR	String to search within
pos	INT	Position to start searching from (optional, 1-based)

Examples

First occurrence

sql

SELECT LOCATE('bar', 'foobarbar');

→-- 4

Starting after pos 5

sql

SELECT LOCATE('bar', 'foobarbar', 5);

→-- 7

On a column

sql

SELECT LOCATE('@', email) FROM users;

→-- position of @ sign

Case-insensitive via LOWER

sql

SELECT LOCATE(LOWER('BAR'), LOWER('FooBarBaz'));

→-- 4

Returns 0 when not found

sql

SELECT LOCATE('x', 'hello');

→-- 0

⚠Anti-Pattern— Testing LOCATE result as boolean

IF(LOCATE('x', col), ...) treats 0 as false and nonzero as true — but LOCATE returns an INT, not a boolean.

✓ Instead: Explicitly test: IF(LOCATE('x', col) > 0, ...) for clarity.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses POSITION(substr IN str) or STRPOS(str, substr); no optional start-pos argument in the standard form.

INSTR

→ INT

Returns the position of the first occurrence of a substring in a string; returns 0 if not found.

Signature

INSTR(str, substr)

Parameters

Parameter	Type	Description
str	VARCHAR	String to search within
substr	VARCHAR	Substring to search for

Examples

Finds 'World' at position 7

sql

SELECT INSTR('Hello World', 'World');

→-- 7

First occurrence only

sql

SELECT INSTR('abcabc', 'bc');

→-- 2

On a column

sql

SELECT INSTR(path, '/') FROM files;

→-- position of first slash

Find last occurrence via REVERSE

sql

SELECT INSTR(REVERSE('hello'), 'l');

→-- 1

NULL handling

sql

SELECT INSTR('abc', NULL);

→-- NULL

⚠Anti-Pattern— Confusing INSTR and LOCATE argument order

INSTR(str, substr) and LOCATE(substr, str) have reversed argument orders — a common source of bugs.

✓ Instead: Pick one function and use it consistently. LOCATE is closer to the SQL standard POSITION syntax.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses STRPOS(str, substr) for the same purpose.

POSITION

→ INT

SQL-standard syntax equivalent to LOCATE(substr, str); returns the position of a substring within a string.

Signature

POSITION(substr IN str)

Parameters

Parameter	Type	Description
substr	VARCHAR	Substring to locate
str	VARCHAR	String to search within

Examples

Standard syntax

sql

SELECT POSITION('bar' IN 'foobar');

→-- 4

Not found returns 0

sql

SELECT POSITION('x' IN 'hello');

→-- 0

On a column

sql

SELECT POSITION('@' IN email) FROM users;

→-- @ position

Nested

sql

SELECT POSITION('o' IN LOWER('FOO'));

→-- 1

NULL handling

sql

SELECT POSITION(NULL IN 'test');

→-- NULL

⚠Anti-Pattern— Forgetting IN keyword

Writing POSITION('x', 'str') (comma syntax) is a syntax error — the IN keyword is mandatory.

✓ Instead: Use POSITION('x' IN 'str') or switch to LOCATE('x', 'str').

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL POSITION(substr IN str) behaves identically.

FIND_IN_SET

→ INT

Returns the 1-based position of a string in a comma-separated list, or 0 if not found.

Signature

FIND_IN_SET(str, strlist)

Parameters

Parameter	Type	Description
str	VARCHAR	String to search for
strlist	VARCHAR	Comma-separated list of values

Examples

Position in list

sql

SELECT FIND_IN_SET('b', 'a,b,c,d');

→-- 2

Not found returns 0

sql

SELECT FIND_IN_SET('e', 'a,b,c,d');

→-- 0

Filter rows

sql

SELECT * FROM t WHERE FIND_IN_SET('admin', roles) > 0;

→-- rows with 'admin' role

Case-insensitive via LOWER

sql

SELECT FIND_IN_SET(LOWER('B'), LOWER('a,B,c'));

→-- 2

NULL handling

sql

SELECT FIND_IN_SET(NULL, 'a,b,c');

→-- NULL

⚠Anti-Pattern— Storing multi-value data as comma-separated strings

Querying comma-separated columns with FIND_IN_SET cannot use indexes and makes JOINs impossible.

✓ Instead: Use a proper junction/bridge table or MariaDB's native SET column type.

◆MariaDB Note

Works natively with SET column type members.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks FIND_IN_SET(); use string_to_array and = ANY().

STRCMP

→ INT

Compares two strings and returns 0 if equal, -1 if the first is less than the second, or 1 if greater.

Signature

STRCMP(str1, str2)

Parameters

Parameter	Type	Description
str1	VARCHAR	First string to compare
str2	VARCHAR	Second string to compare

Examples

Equal strings

sql

SELECT STRCMP('abc', 'abc');

→-- 0

'abc' < 'abd'

sql

SELECT STRCMP('abc', 'abd');

→-- -1

'abd' > 'abc'

sql

SELECT STRCMP('abd', 'abc');

→-- 1

Nested case normalization

sql

SELECT STRCMP(LOWER(a), LOWER(b)) FROM t;

→-- case-insensitive compare

NULL handling

sql

SELECT STRCMP('a', NULL);

→-- NULL

⚠Anti-Pattern— Using STRCMP for equality instead of =

WHERE STRCMP(col, 'value') = 0 is more verbose and slightly slower than WHERE col = 'value'.

✓ Instead: Use = for equality; use STRCMP only when you need the three-way comparison (-1/0/1) for ordering logic.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks STRCMP(); use standard comparison operators or btree-compare expressions.

SUBSTRING_INDEX

→ VARCHAR / TEXT

Returns the substring before the Nth occurrence of a delimiter; negative N counts from the right.

Signature

SUBSTRING_INDEX(str, delim, count)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
delim	VARCHAR	Delimiter to search for
count	INT	Which occurrence to split on; negative = from right

Examples

Before 2nd dot

sql

SELECT SUBSTRING_INDEX('a.b.c.d', '.', 2);

→-- 'a.b'

Last 2 segments

sql

SELECT SUBSTRING_INDEX('a.b.c.d', '.', -2);

→-- 'c.d'

On a column

sql

SELECT SUBSTRING_INDEX(ip, '.', 3) FROM servers;

→-- first 3 octets of IP

Nested to extract Nth element

sql

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(csv, ',', 2), ',', -1) FROM t;

→-- 2nd CSV element

No delimiter found returns whole string

sql

SELECT SUBSTRING_INDEX('hello', '.', 1);

→-- 'hello'

⚠Anti-Pattern— Parsing CSV with SUBSTRING_INDEX in production

Nesting SUBSTRING_INDEX calls for every column in a CSV string is fragile and non-indexable.

✓ Instead: Use JSON_TABLE or normalize the data into separate columns.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks SUBSTRING_INDEX(); use SPLIT_PART(str, delim, n) instead.

INSERT

→ VARCHAR / TEXT

Inserts a substring into a string at a specified position, replacing a given number of characters.

Signature

INSERT(str, pos, len, newstr)

Parameters

Parameter	Type	Description
str	VARCHAR	Original string
pos	INT	1-based position to start replacement
len	INT	Number of characters to replace
newstr	VARCHAR	Replacement string

Examples

Replace 'World'

sql

SELECT INSERT('Hello World', 7, 5, 'MariaDB');

→-- 'Hello MariaDB'

Insert without replacing

sql

SELECT INSERT('abcdef', 3, 0, 'XY');

→-- 'abXYcdef'

Masking sensitive data

sql

SELECT INSERT(card_num, 5, 8, '****') FROM payments;

→-- masked card number

Nested

sql

SELECT INSERT(LPAD('42', 6, '0'), 1, 2, 'XX');

→-- 'XX0042'

NULL len returns NULL

sql

SELECT INSERT('hello', 2, NULL, 'x');

→-- NULL

⚠Anti-Pattern— Confusing INSERT string function with INSERT statement

INSERT(str,...) is a string function, not a DML statement — beginners sometimes confuse the two.

✓ Instead: Remember: string INSERT takes 4 arguments; the SQL INSERT statement is a different keyword.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks INSERT(); use OVERLAY(str PLACING newstr FROM pos FOR len).

ELT

→ VARCHAR

Returns the Nth element from a list of string arguments; returns NULL if N is out of range.

Signature

ELT(n, str1, str2, ...)

Parameters

Parameter	Type	Description
n	INT	1-based index of which string to return
str1, str2, ...	VARCHAR	List of string values to choose from

Examples

Returns 2nd element

sql

SELECT ELT(2, 'a', 'b', 'c');

→-- 'b'

Day name lookup

sql

SELECT ELT(1, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri');

→-- 'Mon'

On a column

sql

SELECT ELT(status_code, 'active', 'inactive', 'banned') FROM users;

→-- status label

Nested with FIELD

sql

SELECT ELT(FIELD('b', 'a', 'b', 'c'), 'x', 'y', 'z');

→-- 'y'

Out of range returns NULL

sql

SELECT ELT(5, 'a', 'b', 'c');

→-- NULL

⚠Anti-Pattern— Using ELT as a substitute for a lookup table

Hardcoding a long list of values in ELT() is brittle and hard to maintain.

✓ Instead: Store lookup data in a reference table and JOIN to it.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks ELT(); use CASE or array indexing: (ARRAY['a','b','c'])[n].

FIELD

→ INT

Returns the 1-based index of the first argument in the subsequent list; returns 0 if not found.

Signature

FIELD(val, str1, str2, ...)

Parameters

Parameter	Type	Description
val	VARCHAR	Value to search for
str1, str2, ...	VARCHAR	List to search within

Examples

Position of 'b'

sql

SELECT FIELD('b', 'a', 'b', 'c');

→-- 2

Not found

sql

SELECT FIELD('x', 'a', 'b', 'c');

→-- 0

Custom ordering

sql

SELECT * FROM t ORDER BY FIELD(status, 'active', 'pending', 'closed');

→-- custom sort order

Case-insensitive via LOWER

sql

SELECT FIELD(LOWER('B'), 'a', 'b', 'c');

→-- 2

NULL returns 0

sql

SELECT FIELD(NULL, 'a', 'b');

→-- 0

⚠Anti-Pattern— Using FIELD for a large number of values

FIELD with 20+ values becomes unreadable and is re-evaluated for every row.

✓ Instead: Use a lookup table with an explicit rank column and JOIN for large ordered sets.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks FIELD(); use array_position(ARRAY[v1,v2,v3], col).

FORMAT

Locale param: MariaDB 10.0.7+→ VARCHAR

Formats a number with a fixed number of decimal places and thousands separators, returning a string.

Signatures

FORMAT(num, decimal_places)

FORMAT(num, decimal_places, locale)

Parameters

Parameter	Type	Description
num	NUMERIC	Number to format
decimal_places	INT	Number of decimal places to display
locale	VARCHAR	Locale string for separators, e.g. 'de_DE' (optional)

Examples

Thousands separator + 2 decimals

sql

SELECT FORMAT(1234567.891, 2);

→-- '1,234,567.89'

No decimal places

sql

SELECT FORMAT(9999, 0);

→-- '9,999'

On a column

sql

SELECT FORMAT(price, 2) FROM products;

→-- formatted prices

German locale

sql

SELECT FORMAT(1234.5, 2, 'de_DE');

→-- '1.234,50'

NULL handling

sql

SELECT FORMAT(NULL, 2);

→-- NULL

⚠Anti-Pattern— Using FORMAT in calculations

SELECT FORMAT(price, 2) * qty will silently cast back to float and lose the formatting intent, or error on locale separators.

✓ Instead: Format numbers only in the SELECT list for display, never in WHERE or JOIN conditions.

◆MariaDB Note

The optional locale parameter was added in MariaDB 10.0.7.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses TO_CHAR(num, 'FM999,999,999.00') for formatted numeric output.

HEX

→ VARCHAR

Returns the hexadecimal string representation of a string or integer argument.

Signatures

HEX(str)

HEX(n)

Parameters

Parameter	Type	Description
str / n	VARCHAR / INT	String to encode or integer to convert to hex

Examples

Hex encoding of string

sql

SELECT HEX('abc');

→-- '616263'

Integer to hex

sql

SELECT HEX(255);

→-- 'FF'

On a column

sql

SELECT HEX(data) FROM blobs;

→-- hex-encoded BLOB

Nested round-trip

sql

SELECT HEX(UNHEX('41'));

→-- '41'

NULL handling

sql

SELECT HEX(NULL);

→-- NULL

⚠Anti-Pattern— Storing hex-encoded data and forgetting to decode it

Querying hex-encoded columns for substring matches fails because the pattern must also be hex-encoded.

✓ Instead: Use UNHEX before comparison or switch to proper BLOB/VARBINARY types.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses ENCODE(data, 'hex') or the \x literal prefix.

UNHEX

→ VARBINARY

Converts a hexadecimal string back to the binary string it represents.

Signature

UNHEX(str)

Parameters

Parameter	Type	Description
str	VARCHAR	Hexadecimal string to decode

Examples

Hex to string

sql

SELECT UNHEX('616263');

→-- 'abc'

Single byte

sql

SELECT UNHEX('FF');

→-- 0xFF byte

On a column

sql

SELECT UNHEX(hex_col) FROM encoded_data;

→-- decoded values

Round-trip

sql

SELECT HEX(UNHEX('4D617269614442'));

→-- '4D617269614442'

Invalid hex returns NULL

sql

SELECT UNHEX('ZZ');

→-- NULL

⚠Anti-Pattern— Passing odd-length hex strings

UNHEX('ABC') has an odd number of hex digits and returns NULL.

✓ Instead: Ensure the hex string is even-length: LPAD(hex_str, CHAR_LENGTH(hex_str) + MOD(CHAR_LENGTH(hex_str),2), '0').

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses DECODE(str, 'hex') or the \x prefix for hex literals.

ASCII

→ INT

Returns the ASCII (numeric) value of the leftmost character of a string.

Signature

ASCII(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String whose first character's ASCII code is returned

Examples

Uppercase A

sql

SELECT ASCII('A');

→-- 65

Lowercase a

sql

SELECT ASCII('a');

→-- 97

Only first character used

sql

SELECT ASCII('Hello');

→-- 72

Round-trip with CHAR

sql

SELECT ASCII(CHAR(65));

→-- 65

NULL handling

sql

SELECT ASCII(NULL);

→-- NULL

⚠Anti-Pattern— Using ASCII for multi-byte Unicode characters

ASCII('é') returns 195 (the first UTF-8 byte), not the Unicode code point 233.

✓ Instead: Use ORD() which returns the full Unicode code point for multi-byte characters.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses ASCII(str) identically for ASCII chars; for full Unicode use CHR() and ASCII().

CHAR

→ VARCHAR

Returns the character(s) corresponding to the given integer code point(s); multi-byte characters supported with USING charset.

Signatures

CHAR(n, ...)

CHAR(n, ... USING charset_name)

Parameters

Parameter	Type	Description
n, ...	INT	One or more integer code point values
charset_name	VARCHAR	Character set to interpret the code points (optional)

Examples

ASCII code 65

sql

SELECT CHAR(65);

→-- 'A'

Multiple chars

sql

SELECT CHAR(72, 101, 108, 108, 111);

→-- 'Hello'

Tab character

sql

SELECT CHAR(9);

→-- '\t'

UTF-8 multi-byte

sql

SELECT CHAR(0xC3, 0xA9 USING utf8mb4);

→-- 'é'

NULL args are skipped

sql

SELECT CHAR(NULL);

→-- ''

⚠Anti-Pattern— Using CHAR without USING for non-ASCII code points

CHAR(233) without a charset clause may return the wrong byte sequence depending on the connection charset.

✓ Instead: Always specify USING utf8mb4 when generating non-ASCII characters.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses CHR(n) for a single code point.

ORD

→ INT

Returns the code point of the leftmost character of a string, handling multi-byte characters correctly.

Signature

ORD(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String whose first character's code point is returned

Examples

ASCII character

sql

SELECT ORD('A');

→-- 65

Unicode code point for é

sql

SELECT ORD('é');

→-- 233

On a column

sql

SELECT ORD(col) FROM t;

→-- code point per row

Round-trip star symbol

sql

SELECT ORD(CHAR(9733));

→-- 9733

NULL handling

sql

SELECT ORD(NULL);

→-- NULL

⚠Anti-Pattern— Using ASCII() on utf8mb4 columns

ASCII('é') returns 195 (first UTF-8 byte), not the human-readable code point 233.

✓ Instead: Use ORD() when the column may contain multi-byte characters.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses ASCII(str) which correctly returns Unicode code points.

BIT_LENGTH

→ INT

Returns the length of a string in bits (8 × byte length).

Signature

BIT_LENGTH(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to measure in bits

Examples

3 bytes × 8 bits

sql

SELECT BIT_LENGTH('abc');

→-- 24

3 bytes in UTF-8 × 8

sql

SELECT BIT_LENGTH('日');

→-- 24

On a column

sql

SELECT BIT_LENGTH(data) FROM messages;

→-- bit sizes

Single byte

sql

SELECT BIT_LENGTH(CHAR(255));

→-- 8

NULL handling

sql

SELECT BIT_LENGTH(NULL);

→-- NULL

⚠Anti-Pattern— Confusing BIT_LENGTH with character count

BIT_LENGTH returns bits, not characters — 'Hello' is 40 bits, not 5.

✓ Instead: Use CHAR_LENGTH() for character count, LENGTH() for byte count, BIT_LENGTH() only for bit-level calculations.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has BIT_LENGTH() with identical behavior.

SOUNDEX

→ VARCHAR

Returns the Soundex phonetic representation of a string, useful for approximate name matching.

Signature

SOUNDEX(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to convert to Soundex code

Examples

Soundex code

sql

SELECT SOUNDEX('Smith');

→-- 'S530'

Same Soundex as Smith

sql

SELECT SOUNDEX('Smythe');

→-- 'S530'

Phonetic search

sql

SELECT * FROM names WHERE SOUNDEX(name) = SOUNDEX('Robert');

→-- Rupert, Roberto, etc.

Nested

sql

SELECT SOUNDEX(LOWER('McDonald'));

→-- 'M235'

NULL handling

sql

SELECT SOUNDEX(NULL);

→-- NULL

⚠Anti-Pattern— Using SOUNDEX for non-English names

Soundex is designed for English phonetics and gives poor results for names in other languages.

✓ Instead: For multilingual fuzzy matching, use the fuzzymatch extension or a dedicated search engine.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL offers SOUNDEX() via the fuzzystrmatch extension.

QUOTE

→ VARCHAR

Returns a string quoted and escaped so it can be used safely as a SQL string literal.

Signature

QUOTE(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to quote and escape

Examples

Basic quoting

sql

SELECT QUOTE('hello');

→-- "'hello'"

Single quote escaped

sql

SELECT QUOTE('it\'s a test');

→-- "'it\'s a test'"

On a column

sql

SELECT QUOTE(user_input) FROM logs;

→-- safely quoted values

Nested

sql

SELECT QUOTE(CONCAT('foo', 'bar'));

→-- "'foobar'"

NULL returns the string 'NULL'

sql

SELECT QUOTE(NULL);

→-- 'NULL'

⚠Anti-Pattern— Relying on QUOTE() to prevent SQL injection from application code

QUOTE() is designed for use inside SQL (e.g., stored procedures), not as an application-layer sanitizer.

✓ Instead: Use parameterized queries / prepared statements in your application code.

◆MariaDB Note

QUOTE(NULL) returns the unquoted string 'NULL', unlike most functions that return NULL.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses QUOTE_LITERAL() and QUOTE_NULLABLE() for the same purpose.

CHARSET

→ VARCHAR

Returns the character set name of a string argument.

Signature

CHARSET(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String whose character set is inspected

Examples

Default connection charset

sql

SELECT CHARSET('hello');

→-- 'utf8mb4'

After conversion

sql

SELECT CHARSET(CONVERT('hello' USING latin1));

→-- 'latin1'

On a column

sql

SELECT CHARSET(name) FROM customers;

→-- charset of column data

Nested

sql

SELECT CHARSET(LOWER('ABC'));

→-- 'utf8mb4'

NULL returns 'binary'

sql

SELECT CHARSET(NULL);

→-- 'binary'

⚠Anti-Pattern— Assuming all string columns share the same charset

Joining a utf8mb4 column with a latin1 column causes implicit conversion and can break index use.

✓ Instead: Check CHARSET(col) and align column charsets at table-design time.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has no CHARSET(); the database has a single encoding checked with pg_encoding_to_char(encoding).

COERCIBILITY

→ INT

Returns the collation coercibility value of a string expression (lower = higher priority in coercion).

Signature

COERCIBILITY(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String expression to inspect

Examples

Coercible (default)

sql

SELECT COERCIBILITY('hello');

→-- 4

Explicit charset literal

sql

SELECT COERCIBILITY(_utf8mb4'hello');

→-- 4

On a column

sql

SELECT COERCIBILITY(col) FROM t;

→-- coercibility per column

Nested

sql

SELECT COERCIBILITY(LOWER('ABC'));

→-- 4

NULL has coercibility 6

sql

SELECT COERCIBILITY(NULL);

→-- 6

⚠Anti-Pattern— Ignoring coercibility when mixing collations

Comparing two columns with different collations and equal coercibility causes a runtime error.

✓ Instead: Add an explicit COLLATE clause: col1 = col2 COLLATE utf8mb4_unicode_ci.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL handles collation precedence differently — no equivalent function.

COLLATION

→ VARCHAR

Returns the collation of a string argument.

Signature

COLLATION(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String expression whose collation is returned

Examples

Connection default

sql

SELECT COLLATION('hello');

→-- 'utf8mb4_general_ci'

After CONVERT

sql

SELECT COLLATION(CONVERT('hello' USING utf8mb4));

→-- 'utf8mb4_general_ci'

On a column

sql

SELECT COLLATION(name) FROM users;

→-- collation of column

Explicit collate

sql

SELECT COLLATION('hello' COLLATE utf8mb4_bin);

→-- 'utf8mb4_bin'

NULL returns 'binary'

sql

SELECT COLLATION(NULL);

→-- 'binary'

⚠Anti-Pattern— Assuming default collation is case-sensitive

Many MariaDB defaults use _ci (case-insensitive) collations, so 'ABC' = 'abc' is TRUE.

✓ Instead: Check COLLATION(col) and use _bin or _cs collations when case-sensitivity matters.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has no COLLATION() function; check pg_attribute or pg_collation catalog.

REGEXP_REPLACE

MariaDB 10.0.5+→ VARCHAR / TEXT

Replaces substrings matching a regular expression with a replacement string.

Signatures

REGEXP_REPLACE(str, pattern, replace)

REGEXP_REPLACE(str, pattern, replace, pos)

REGEXP_REPLACE(str, pattern, replace, pos, occurrence)

REGEXP_REPLACE(str, pattern, replace, pos, occurrence, match_type)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
pattern	VARCHAR	Regular expression pattern
replace	VARCHAR	Replacement string; use \1-\9 for back-references
pos	INT	Position to start (optional, default 1)
occurrence	INT	Which occurrence to replace; 0 = all (optional)
match_type	VARCHAR	Flags: i=case-insensitive, c=case-sensitive, m=multiline, n=. matches newline (optional)

Examples

Replace all matches

sql

SELECT REGEXP_REPLACE('Hello World', 'o', '0');

→-- 'Hell0 W0rld'

Replace digit sequence

sql

SELECT REGEXP_REPLACE('foo123bar', '[0-9]+', '#');

→-- 'foo#bar'

Strip non-digits from column

sql

SELECT REGEXP_REPLACE(phone, '[^0-9]', '') FROM contacts;

→-- digits only

Case-insensitive flag

sql

SELECT REGEXP_REPLACE('aAbBcC', '[a-z]', '_', 1, 0, 'i');

→-- '______'

NULL handling

sql

SELECT REGEXP_REPLACE(NULL, 'x', 'y');

→-- NULL

⚠Anti-Pattern— Using REGEXP_REPLACE when REPLACE() suffices

REGEXP_REPLACE is slower than REPLACE for simple literal substitutions.

✓ Instead: Use REPLACE() for literal find-and-replace; reserve REGEXP_REPLACE for pattern-based work.

◆MariaDB Note

Available since MariaDB 10.0.5 using the PCRE library.

⇄ vs MySQL: MySQL added REGEXP_REPLACE in 8.0; MariaDB has had it since 10.0.5.

⇄ vs PostgreSQL: PostgreSQL uses REGEXP_REPLACE(str, pattern, replacement, flags) with similar semantics.

REGEXP_SUBSTR

MariaDB 10.0.5+→ VARCHAR / TEXT

Returns the substring of a string that matches a regular expression pattern.

Signatures

REGEXP_SUBSTR(str, pattern)

REGEXP_SUBSTR(str, pattern, pos)

REGEXP_SUBSTR(str, pattern, pos, occurrence)

REGEXP_SUBSTR(str, pattern, pos, occurrence, match_type)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
pattern	VARCHAR	Regular expression pattern
pos	INT	Starting position (optional, default 1)
occurrence	INT	Which occurrence to return (optional, default 1)
match_type	VARCHAR	Flags: i, c, m, n (optional)

Examples

Extract digit sequence

sql

SELECT REGEXP_SUBSTR('abc123def', '[0-9]+');

→-- '123'

Extract year

sql

SELECT REGEXP_SUBSTR('2026-03-09', '[0-9]{4}');

→-- '2026'

On a column

sql

SELECT REGEXP_SUBSTR(email, '[^@]+') FROM users;

→-- local part of email

Second occurrence

sql

SELECT REGEXP_SUBSTR('aaa bbb ccc', '[a-z]+', 1, 2);

→-- 'bbb'

No match returns NULL

sql

SELECT REGEXP_SUBSTR('hello', 'x+');

→-- NULL

⚠Anti-Pattern— Using REGEXP_SUBSTR to extract a known-position field

If the position is fixed (e.g. the 2nd CSV field), REGEXP_SUBSTR is overkill.

✓ Instead: Use SUBSTRING_INDEX for delimiter-based extraction when the pattern is simple.

◆MariaDB Note

Available since MariaDB 10.0.5.

⇄ vs MySQL: MySQL added REGEXP_SUBSTR in 8.0; MariaDB has had it since 10.0.5.

⇄ vs PostgreSQL: PostgreSQL uses SUBSTRING(str FROM pattern) or REGEXP_MATCH(str, pattern) for similar extraction.

REGEXP_INSTR

MariaDB 10.0.5+→ INT

Returns the starting position of the substring in a string that matches a regular expression.

Signatures

REGEXP_INSTR(str, pattern)

REGEXP_INSTR(str, pattern, pos)

REGEXP_INSTR(str, pattern, pos, occurrence)

REGEXP_INSTR(str, pattern, pos, occurrence, return_option)

REGEXP_INSTR(str, pattern, pos, occurrence, return_option, match_type)

Parameters

Parameter	Type	Description
str	VARCHAR	Source string
pattern	VARCHAR	Regular expression pattern
pos	INT	Start position (optional, default 1)
occurrence	INT	Which occurrence (optional, default 1)
return_option	INT	0=start of match, 1=position after match (optional)
match_type	VARCHAR	Flags (optional)

Examples

Position of first 'o'

sql

SELECT REGEXP_INSTR('Hello World', 'o');

→-- 5

First digit position

sql

SELECT REGEXP_INSTR('abc123', '[0-9]');

→-- 4

On a column

sql

SELECT REGEXP_INSTR(text, '[A-Z]') FROM docs;

→-- first uppercase pos

Position after match

sql

SELECT REGEXP_INSTR('aabbcc', 'b+', 1, 1, 1);

→-- 5

No match returns 0

sql

SELECT REGEXP_INSTR('hello', 'x');

→-- 0

⚠Anti-Pattern— Using REGEXP_INSTR where LOCATE suffices

For literal substring searches, REGEXP_INSTR adds overhead with no benefit.

✓ Instead: Use LOCATE or INSTR for literal searches; REGEXP_INSTR only when you need pattern matching.

◆MariaDB Note

Available since MariaDB 10.0.5.

⇄ vs MySQL: MySQL added REGEXP_INSTR in 8.0; MariaDB has had it since 10.0.5.

⇄ vs PostgreSQL: PostgreSQL has no direct REGEXP_INSTR(); use REGEXP_MATCH() and array indexing.

REGEXP_LIKE

MariaDB 10.0.5+→ INT (0 or 1)

Returns 1 if a string matches a regular expression pattern, 0 otherwise.

Signatures

REGEXP_LIKE(str, pattern)

REGEXP_LIKE(str, pattern, match_type)

Parameters

Parameter	Type	Description
str	VARCHAR	String to test
pattern	VARCHAR	Regular expression pattern
match_type	VARCHAR	Flags: i=case-insensitive, c, m, n (optional)

Examples

Starts with H

sql

SELECT REGEXP_LIKE('Hello', '^H');

→-- 1

Letters then digits

sql

SELECT REGEXP_LIKE('abc123', '^[a-z]+[0-9]+
    
  

);

→-- 1

Email validation filter

sql

SELECT * FROM users WHERE REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+
    
  

);

→-- valid email rows

Case-insensitive flag

sql

SELECT REGEXP_LIKE('Hello', 'hello', 'i');

→-- 1

NULL handling

sql

SELECT REGEXP_LIKE(NULL, 'x');

→-- NULL

⚠Anti-Pattern— Using REGEXP_LIKE for simple prefix/suffix checks

REGEXP_LIKE(col, '^prefix') is slower and less readable than col LIKE 'prefix%'.

✓ Instead: Use LIKE for simple wildcards; reserve REGEXP_LIKE for patterns that LIKE cannot express.

◆MariaDB Note

Available since MariaDB 10.0.5.

⇄ vs MySQL: MySQL added REGEXP_LIKE in 8.0; MariaDB has had REGEXP/RLIKE since early versions and REGEXP_LIKE since 10.0.5.

⇄ vs PostgreSQL: PostgreSQL uses the ~ operator or REGEXP_MATCH() for regex testing.

TO_BASE64

MariaDB 10.0+→ VARCHAR / TEXT

Encodes a string to Base64 format.

Signature

TO_BASE64(str)

Parameters

Parameter	Type	Description
str	VARCHAR / BLOB	String or binary data to Base64-encode

Examples

Basic Base64 encoding

sql

SELECT TO_BASE64('Hello');

→-- 'SGVsbG8='

Another example

sql

SELECT TO_BASE64('MariaDB');

→-- 'TWFyaWFEQg=='

Encoding BLOB column

sql

SELECT TO_BASE64(avatar) FROM users;

→-- base64-encoded image data

Round-trip

sql

SELECT FROM_BASE64(TO_BASE64('test'));

→-- 'test'

NULL handling

sql

SELECT TO_BASE64(NULL);

→-- NULL

⚠Anti-Pattern— Storing Base64 in TEXT columns for binary safety

Base64 inflation means a 1 MB image becomes a 1.37 MB string, bloating row sizes and slowing queries.

✓ Instead: Use BLOB/MEDIUMBLOB columns for binary data and let the driver handle encoding.

◆MariaDB Note

Added in MariaDB 10.0.

⇄ vs MySQL: Identical to MySQL (added in MySQL 5.6 / MariaDB 10.0).

⇄ vs PostgreSQL: PostgreSQL uses ENCODE(data, 'base64') for the same purpose.

FROM_BASE64

MariaDB 10.0+→ VARBINARY

Decodes a Base64-encoded string back to its original binary form.

Signature

FROM_BASE64(str)

Parameters

Parameter	Type	Description
str	VARCHAR	Base64-encoded string to decode

Examples

Decode Base64

sql

SELECT FROM_BASE64('SGVsbG8=');

→-- 'Hello'

Another decode

sql

SELECT FROM_BASE64('TWFyaWFEQg==');

→-- 'MariaDB'

On a column

sql

SELECT FROM_BASE64(encoded) FROM assets;

→-- decoded binary

Round-trip length check

sql

SELECT CHAR_LENGTH(FROM_BASE64(TO_BASE64('test')));

→-- 4

Invalid Base64 returns NULL

sql

SELECT FROM_BASE64('not-valid!!');

→-- NULL

⚠Anti-Pattern— Assuming FROM_BASE64 always returns valid text

FROM_BASE64 of binary data returns raw bytes that may not be valid in the connection charset.

✓ Instead: Use CONVERT(FROM_BASE64(col) USING utf8mb4) when you know the original was UTF-8 text.

◆MariaDB Note

Added in MariaDB 10.0.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses DECODE(str, 'base64').

EXPORT_SET

→ VARCHAR

Returns a string where each bit in a numeric value maps to an 'on' or 'off' string, separated by a delimiter.

Signatures

EXPORT_SET(bits, on, off)

EXPORT_SET(bits, on, off, separator)

EXPORT_SET(bits, on, off, separator, number_of_bits)

Parameters

Parameter	Type	Description
bits	BIGINT UNSIGNED	Integer whose bits are examined
on	VARCHAR	String to use for set bits
off	VARCHAR	String to use for unset bits
separator	VARCHAR	Delimiter between values (default ',')
number_of_bits	INT	Number of bits to process (default 64)

Examples

5 = 0101 in binary

sql

SELECT EXPORT_SET(5, 'Y', 'N', ',', 4);

→-- 'Y,N,Y,N'

Binary representation

sql

SELECT EXPORT_SET(7, '1', '0', '', 4);

→-- '1110'

On a column

sql

SELECT EXPORT_SET(perms, 'R', '-', '') FROM roles;

→-- permission bitmask display

6 = 0110

sql

SELECT EXPORT_SET(6, 'on', 'off', '|', 4);

→-- 'off|on|on|off'

NULL handling

sql

SELECT EXPORT_SET(NULL, 'Y', 'N');

→-- NULL

⚠Anti-Pattern— Using EXPORT_SET as a general array-to-string function

EXPORT_SET only works with bit positions in an integer — it is not a general-purpose array serializer.

✓ Instead: Use GROUP_CONCAT or JSON_ARRAYAGG for turning row sets into delimited strings.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks EXPORT_SET(); use bit string operations or array functions.

MAKE_SET

→ VARCHAR

Returns a comma-separated string containing the strings at bit positions that are set in the given bits integer.

Signature

MAKE_SET(bits, str1, str2, ...)

Parameters

Parameter	Type	Description
bits	BIGINT UNSIGNED	Bitmask indicating which strings to include
str1, str2, ...	VARCHAR	Strings corresponding to each bit position

Examples

bits 1 and 3 set (5 = 101)

sql

SELECT MAKE_SET(5, 'a', 'b', 'c');

→-- 'a,c'

All bits set

sql

SELECT MAKE_SET(7, 'read', 'write', 'exec');

→-- 'read,write,exec'

On a column

sql

SELECT MAKE_SET(perms, 'read', 'write', 'delete') FROM roles;

→-- permission strings

Only bit 2 set

sql

SELECT MAKE_SET(2, 'x', 'y', 'z');

→-- 'y'

NULL entries skipped

sql

SELECT MAKE_SET(5, 'a', NULL, 'c');

→-- 'a,c'

⚠Anti-Pattern— Using MAKE_SET with more than 64 string arguments

MAKE_SET uses a BIGINT bitmask, so it supports at most 64 bit positions.

✓ Instead: For more than 64 flags, use a SET column, JSON column, or a junction table.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks MAKE_SET(); use arrays and array_to_string().

OCT

→ VARCHAR

Returns the octal string representation of a number.

Signature

OCT(n)

Parameters

Parameter	Type	Description
n	BIGINT UNSIGNED	Integer to convert to octal string

Examples

8 in octal

sql

SELECT OCT(8);

→-- '10'

255 in octal

sql

SELECT OCT(255);

→-- '377'

On a column

sql

SELECT OCT(perm_bits) FROM files;

→-- UNIX permission octal

Hex to octal via CONV

sql

SELECT OCT(CONV('FF', 16, 10));

→-- '377'

NULL handling

sql

SELECT OCT(NULL);

→-- NULL

⚠Anti-Pattern— Confusing octal output with a numeric value

OCT returns a VARCHAR string. Adding 1 to the result does string concatenation, not arithmetic.

✓ Instead: Use CONV() when you need to perform further arithmetic in a different base.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks OCT(); use TO_OCTAL(n) (PG 16+) or LTRIM(TO_CHAR(n,'OOOOOO'),'0').

BIN

→ VARCHAR

Returns the binary string representation of a number.

Signature

BIN(n)

Parameters

Parameter	Type	Description
n	BIGINT UNSIGNED	Integer to convert to binary string

Examples

10 in binary

sql

SELECT BIN(10);

→-- '1010'

255 in binary

sql

SELECT BIN(255);

→-- '11111111'

On a column

sql

SELECT BIN(flags) FROM settings;

→-- binary representation of flags

Hex to binary via CONV

sql

SELECT BIN(CONV('FF', 16, 10));

→-- '11111111'

NULL handling

sql

SELECT BIN(NULL);

→-- NULL

⚠Anti-Pattern— Treating BIN output as an integer

BIN returns a VARCHAR. Arithmetic on the result treats it as a string/integer cast, not a binary number.

✓ Instead: Use CONV(BIN(n), 2, 10) to convert back to decimal, or stay in the integer domain with bitwise operators.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL lacks BIN(); use TO_BIN(n) (PG 16+) or LTRIM(CAST(n::bit(64) AS TEXT), '0').

NATURAL_SORT_KEY

MariaDB 10.7.1+→ VARBINARY

Returns a sort key that produces natural (human) ordering so that 'item2' sorts before 'item10'.

Signature

NATURAL_SORT_KEY(str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to generate a natural sort key for

Examples

10 > 9 numerically

sql

SELECT NATURAL_SORT_KEY('item10') > NATURAL_SORT_KEY('item9');

→-- 1 (TRUE)

Natural file sort

sql

SELECT name FROM files ORDER BY NATURAL_SORT_KEY(name);

→-- file1, file2, ..., file10

Version sorting

sql

SELECT NATURAL_SORT_KEY(version) FROM releases ORDER BY 1;

→-- v1.2, v1.10, v2.0

Nested

sql

SELECT NATURAL_SORT_KEY(CONCAT('item', n)) FROM t;

→-- sort keys

NULL handling

sql

SELECT NATURAL_SORT_KEY(NULL);

→-- NULL

⚠Anti-Pattern— Using lexicographic ORDER BY for alphanumeric data

ORDER BY filename sorts 'file10' before 'file2' because '1' < '2' lexicographically.

✓ Instead: Use ORDER BY NATURAL_SORT_KEY(filename) for human-expected ordering.

◆MariaDB Note

MariaDB-specific function, not available in MySQL.

⇄ vs MySQL: Not available in MySQL — this is a MariaDB-specific function.

⇄ vs PostgreSQL: PostgreSQL lacks a built-in natural sort key function; use the pg_natural_sort_order extension or a custom function.

LOAD_FILE

→ LONGBLOB / TEXT

Reads and returns the contents of a file from the server filesystem; requires FILE privilege.

Signature

LOAD_FILE(file_name)

Parameters

Parameter	Type	Description
file_name	VARCHAR	Absolute path to the file on the server filesystem

Examples

Read a server-side file

sql

SELECT LOAD_FILE('/tmp/data.txt');

→-- file contents as string

File size check

sql

SELECT CHAR_LENGTH(LOAD_FILE('/etc/hostname'));

→-- length of hostname file

Load file into table

sql

INSERT INTO docs(content) SELECT LOAD_FILE('/tmp/report.txt');

→-- inserts file content

Dynamic path from column

sql

SELECT LOAD_FILE(CONCAT('/var/data/', filename)) FROM uploads;

→-- dynamic path

File not found returns NULL

sql

SELECT LOAD_FILE('/nonexistent');

→-- NULL

⚠Anti-Pattern— Using LOAD_FILE with user-supplied paths

Allowing user input to control the file path is a path traversal vulnerability.

✓ Instead: Always use hard-coded or tightly validated server-side paths with LOAD_FILE.

◆MariaDB Note

Requires FILE privilege and the file must be within the secure_file_priv directory if set.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses pg_read_file() (superuser only) or COPY for similar server-side file reading.

SFORMAT

MariaDB 10.7+→ VARCHAR

Formats a string using Python-style {} placeholder syntax (requires the fmt library).

Signature

SFORMAT(format_str, arg1, arg2, ...)

Parameters

Parameter	Type	Description
format_str	VARCHAR	Format string with {} or {:format_spec} placeholders
arg1, arg2, ...	ANY	Values substituted into the placeholders

Examples

Basic placeholder

sql

SELECT SFORMAT('Hello {}!', 'World');

→-- 'Hello World!'

Multiple placeholders

sql

SELECT SFORMAT('{} + {} = {}', 1, 2, 3);

→-- '1 + 2 = 3'

Float format spec

sql

SELECT SFORMAT('Value: {:.2f}', 3.14159);

→-- 'Value: 3.14'

From columns

sql

SELECT SFORMAT(template, val) FROM messages;

→-- formatted messages

Positional reuse

sql

SELECT SFORMAT('Hi {0} and {0}', 'Bob');

→-- 'Hi Bob and Bob'

⚠Anti-Pattern— Using SFORMAT with untrusted format strings

Allowing user-supplied format strings could expose unexpected data or cause errors.

✓ Instead: Always use hard-coded format strings; only substitute values through the arguments.

◆MariaDB Note

MariaDB-specific function requiring the fmt library. Available since MariaDB 10.7.

⇄ vs MySQL: Not available in MySQL — this is a MariaDB-specific function.

⇄ vs PostgreSQL: PostgreSQL has no SFORMAT(); use FORMAT() with %s, %I, %L placeholders.

WEIGHT_STRING

→ VARBINARY

Returns the sort key (weight string) for a string value under the current collation, useful for debugging collation behavior.

Signatures

WEIGHT_STRING(str)

WEIGHT_STRING(str AS CHAR(n))

WEIGHT_STRING(str AS BINARY(n))

Parameters

Parameter	Type	Description
str	VARCHAR	String to generate the weight string for
n	INT	Cast width (optional)

Examples

Weight of lowercase a

sql

SELECT HEX(WEIGHT_STRING('a'));

→-- collation-dependent weight

Same weight under case-insensitive collation

sql

SELECT HEX(WEIGHT_STRING('A'));

→-- same as 'a' under _ci

Padded to 5 chars

sql

SELECT HEX(WEIGHT_STRING('abc' AS CHAR(5)));

→-- padded weight

Inspect sort order

sql

SELECT HEX(WEIGHT_STRING(name)) FROM t ORDER BY 1;

→-- collation order

NULL handling

sql

SELECT WEIGHT_STRING(NULL);

→-- NULL

⚠Anti-Pattern— Using WEIGHT_STRING in production queries for comparison

WEIGHT_STRING is a diagnostic/debugging function, not a comparison operator.

✓ Instead: Use COLLATE clauses or change column collation for production ordering and comparison needs.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL has no WEIGHT_STRING(); use COLLATION catalog views for debugging.

CONVERT

→ VARCHAR / BINARY

Converts a value to a different data type or character set.

Signatures

CONVERT(value, type)

CONVERT(value USING charset_name)

Parameters

Parameter	Type	Description
value	ANY	Value to convert
type	TYPE	Target data type (e.g. CHAR, SIGNED, UNSIGNED, BINARY)
charset_name	VARCHAR	Target character set for the USING form

Examples

String to integer

sql

SELECT CONVERT('123', SIGNED);

→-- 123

Re-encode charset

sql

SELECT CONVERT('hello' USING utf8mb4);

→-- 'hello' in utf8mb4

Float to string

sql

SELECT CONVERT(3.14, CHAR);

→-- '3.14'

Binary to text

sql

SELECT CONVERT(UNHEX('41') USING utf8mb4);

→-- 'A'

NULL handling

sql

SELECT CONVERT(NULL, CHAR);

→-- NULL

⚠Anti-Pattern— Using CONVERT to cast in arithmetic instead of CAST

CONVERT(str, SIGNED) and CAST(str AS SIGNED) are equivalent, but mixing both in a codebase is confusing.

✓ Instead: Standardize on CAST() for type conversions and CONVERT(...USING...) for charset conversions.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL uses CAST(value AS type) or value::type; no USING charset form.

DECODE

→ VARBINARY

Decrypts a string that was encrypted with ENCODE() using a given password string (deprecated; not cryptographically secure).

Signature

DECODE(crypt_str, pass_str)

Parameters

Parameter	Type	Description
crypt_str	VARBINARY	Encrypted string produced by ENCODE()
pass_str	VARCHAR	Password used for decryption

Examples

Round-trip encode/decode

sql

SELECT DECODE(ENCODE('secret', 'key'), 'key');

→-- 'secret'

On a column

sql

SELECT DECODE(encoded_col, 'mypassword') FROM vault;

→-- decoded values

Nested length check

sql

SELECT CHAR_LENGTH(DECODE(ENCODE('hi', 'k'), 'k'));

→-- 2

Wrong password gives garbage

sql

SELECT DECODE(ENCODE('test', 'pw'), 'wrong');

→-- garbled bytes

NULL handling

sql

SELECT DECODE(NULL, 'key');

→-- NULL

⚠Anti-Pattern— Using DECODE/ENCODE for production security

ENCODE/DECODE are not cryptographically secure and are deprecated in MariaDB.

✓ Instead: Use AES_ENCRYPT() / AES_DECRYPT() with AES_256_CBC mode and a strong key for real encryption.

◆MariaDB Note

Deprecated. Use AES_ENCRYPT/AES_DECRYPT instead.

⇄ vs MySQL: Identical to MySQL; deprecated in both.

⇄ vs PostgreSQL: PostgreSQL lacks DECODE/ENCODE for encryption; use pgcrypto extension functions.

ENCODE

→ VARBINARY

Encrypts a string using a password with a simple XOR-based algorithm (deprecated; not cryptographically secure).

Signature

ENCODE(str, pass_str)

Parameters

Parameter	Type	Description
str	VARCHAR	String to encrypt
pass_str	VARCHAR	Password string

Examples

Encode and view as hex

sql

SELECT HEX(ENCODE('hello', 'key'));

→-- hex of encrypted bytes

On a column

sql

SELECT ENCODE(sensitive, 'pass') FROM data;

→-- encrypted VARBINARY

Same length as input

sql

SELECT OCTET_LENGTH(ENCODE('test', 'pw'));

→-- 4

Round-trip

sql

SELECT DECODE(ENCODE('msg', 'k'), 'k');

→-- 'msg'

NULL handling

sql

SELECT ENCODE(NULL, 'key');

→-- NULL

⚠Anti-Pattern— Storing ENCODE output in a VARCHAR column

ENCODE returns binary data that may contain null bytes, which truncates VARCHAR storage.

✓ Instead: Store ENCODE output in a VARBINARY or BLOB column, or better yet use AES_ENCRYPT.

◆MariaDB Note

Deprecated. Use AES_ENCRYPT/AES_DECRYPT instead.

⇄ vs MySQL: Identical to MySQL; deprecated in both.

⇄ vs PostgreSQL: PostgreSQL lacks this ENCODE(); use pgcrypto.

Related MariaDB Categories

Date & Time →Aggregate Functions →JSON Functions →

MariaDB String Functions

Jump to function

What are MariaDB String Functions?

CONCAT

Signature

Parameters

Examples

CONCAT_WS

Signature

Parameters

Examples

LENGTH

Signature

Parameters

Examples

CHAR_LENGTH

Signatures

Parameters

Examples

CHARACTER_LENGTH

Signature

Parameters

Examples

OCTET_LENGTH

Signature

Parameters

Examples

SUBSTRING

Signatures

Parameters

Examples

SUBSTR

Signatures

Parameters

Examples

MID

Signature

Parameters

Examples

LEFT

Signature

Parameters

Examples

RIGHT

Signature

Parameters

Examples

TRIM

Signatures

Parameters

Examples

LTRIM

Signature

Parameters

Examples

RTRIM

Signature

Parameters

Examples

LPAD

Signature

Parameters

Examples

RPAD

Signature

Parameters

Examples

UPPER

Signature

Parameters

Examples

LOWER

Signature

Parameters

Examples

UCASE

Signature

Parameters

Examples

LCASE