🔤

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

MariaDB 5.5+→ 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).

CONCAT returns NULL if ANY argument is NULL. Wrap nullable columns with COALESCE to get an empty string fallback. This is the most common source of silently missing data in report queries.

example

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

→-- 'Jane Doe' even when first_name is NULL

◆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

MariaDB 5.5+→ 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.

CONCAT_WS skips NULL arguments automatically, so CONCAT_WS(', ', city, state, zip) never produces a leading or trailing comma when a column is NULL. Ideal for address formatting and dynamic tag lists.

example

SELECT CONCAT_WS(', ', city, state, country) FROM addresses;

→-- 'London, England' (NULL state skipped)

◆MariaDB Note

Standard SQL function available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LENGTH

MariaDB 5.5+→ 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.

LENGTH counts bytes, not characters. Under utf8mb4, a single emoji occupies 4 bytes — a 10-emoji username would return LENGTH() = 40 but CHAR_LENGTH() = 10. Always use CHAR_LENGTH for user-visible size limits.

example

SELECT name, LENGTH(name) AS bytes, CHAR_LENGTH(name) AS chars FROM products;

→-- 'café', 5, 4

◆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

MariaDB 5.5+→ 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.

CHAR_LENGTH counts Unicode code points regardless of encoding, making it the right function for validating user-input length. It avoids the multi-byte trap of LENGTH() under utf8mb4.

example

SELECT * FROM users WHERE CHAR_LENGTH(username) > 20;

→-- rows where display length exceeds 20

◆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

MariaDB 5.5+→ 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.

CHARACTER_LENGTH is the SQL-standard name; CHAR_LENGTH is the shorthand. Both return identical results. Prefer CHARACTER_LENGTH when writing portable SQL that may run on PostgreSQL or other databases.

example

SELECT CHARACTER_LENGTH('Héllo');

→-- 5

◆MariaDB Note

Exact synonym for CHAR_LENGTH().

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: Identical behavior in PostgreSQL.

OCTET_LENGTH

MariaDB 5.5+→ 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.

OCTET_LENGTH is a synonym for LENGTH in MariaDB, but using it makes your intent explicit: you're counting bytes for storage or protocol-size purposes, not character count. Useful when sizing VARCHAR columns for wire-protocol budgets.

example

SELECT OCTET_LENGTH(bio) FROM profiles WHERE OCTET_LENGTH(bio) > 65535;

→-- rows exceeding TEXT storage threshold

◆MariaDB Note

Exact synonym for LENGTH().

⇄ vs MySQL: Identical to MySQL.

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

SUBSTRING

MariaDB 5.5+→ 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.

SUBSTRING(str, -N) counts from the end of the string — no need to compute LEN-N first. This is the cleanest way to extract file extensions or domain suffixes.

example

SELECT SUBSTRING(filename, -3) FROM files;

→-- 'pdf', 'jpg', 'txt'

◆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

MariaDB 5.5+→ 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.

SUBSTR and SUBSTRING are 100% interchangeable in MariaDB. Choose one convention for your codebase; mixing them makes grep-based searches harder. SUBSTRING is more explicit and closer to the SQL standard.

example

SELECT SUBSTR(phone, 1, 3) AS area_code FROM contacts;

→-- '555'

◆MariaDB Note

Exact synonym for SUBSTRING() and MID().

⇄ vs MySQL: Identical to MySQL.

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

MID

MariaDB 5.5+→ 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.

MID(str, pos, len) is an alias for SUBSTRING(str, pos, len). It exists for MySQL compatibility. New code should prefer SUBSTRING, which also works in PostgreSQL and other SQL dialects.

example

SELECT MID(order_ref, 5, 8) FROM orders;

→-- 8-char segment starting at position 5

◆MariaDB Note

Exact synonym for SUBSTRING().

⇄ vs MySQL: Identical to MySQL.

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

LEFT

MariaDB 5.5+→ 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.

LEFT(str, n) is the cleanest way to truncate a string to a maximum display width. For utf8mb4 columns it counts characters, not bytes, so it's safe for international text. Use it for preview snippets and slug generation.

example

SELECT LEFT(title, 50) AS preview FROM articles;

→-- first 50 characters of title

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

RIGHT

MariaDB 5.5+→ 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.

RIGHT(str, n) is the mirror of LEFT. Combining it with LOCATE or CHAR_LENGTH lets you pull the extension from a filename without a regex: RIGHT(filename, CHAR_LENGTH(filename) - LOCATE('.', filename)).

example

SELECT RIGHT(email, CHAR_LENGTH(email) - LOCATE('@', email)) AS domain FROM users;

→-- 'example.com'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

TRIM

MariaDB 5.5+→ 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.

TRIM can strip any character, not just spaces: TRIM(LEADING '/' FROM path) removes leading slashes. This is cleaner than chaining LTRIM/RTRIM when you need to strip a specific delimiter.

example

SELECT TRIM(BOTH '"' FROM raw_value) FROM imports;

→-- 'hello' (quotes stripped from '"hello"')

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LTRIM

MariaDB 5.5+→ 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.

User-input data often has leading spaces that cause WHERE col = 'value' to miss matches. Apply LTRIM in an UPDATE to clean up imported data rather than repeating the workaround in every query.

example

UPDATE contacts SET name = LTRIM(name) WHERE name != LTRIM(name);

→-- rows with leading spaces cleaned

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

RTRIM

MariaDB 5.5+→ 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.

CHAR columns are space-padded to their declared length; RTRIM removes this padding when you need to compare or display the raw value. VARCHAR does not need this, but legacy CHAR migrations often do.

example

SELECT RTRIM(product_code) AS code FROM legacy_products;

→-- 'ABC123' not 'ABC123 '

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LPAD

MariaDB 5.5+→ 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.

LPAD is the standard way to left-pad invoice numbers, order IDs, or version strings so that alphabetic sort equals numeric sort. Always specify the full target length to avoid silent truncation.

example

SELECT LPAD(invoice_id, 8, '0') AS padded_id FROM invoices;

→-- '00001234'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL LPAD() is identical.

RPAD

MariaDB 5.5+→ 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.

RPAD is useful for generating fixed-width export files where columns must occupy an exact number of characters. Combine with SUBSTR to cap length and then pad in one pass.

example

SELECT RPAD(LEFT(description, 30), 30, ' ') AS fixed_col FROM products;

→-- exactly 30 chars, truncated then padded

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL RPAD() is identical.

UPPER

MariaDB 5.5+→ 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'.

Running UPPER in every SELECT query adds CPU overhead on large tables. Better to store case-normalized values (or use a case-insensitive collation) and apply UPPER only during INSERT/UPDATE.

example

UPDATE users SET country_code = UPPER(country_code) WHERE country_code != UPPER(country_code);

→-- 'us' -> 'US', 'gb' -> 'GB'

◆MariaDB Note

UCASE() is a synonym.

⇄ vs MySQL: Identical to MySQL.

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

LOWER

MariaDB 5.5+→ 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.

Email addresses should be stored in lowercase to prevent 'User@Example.com' and 'user@example.com' appearing as different accounts. Apply LOWER during INSERT and in a one-time data-cleanse UPDATE.

example

INSERT INTO users (email) VALUES (LOWER(TRIM(?)));

→-- 'User@Example.com' stored as 'user@example.com'

◆MariaDB Note

LCASE() is a synonym.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL LOWER() behaves identically.

UCASE

MariaDB 5.5+→ 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.

UCASE and UPPER are identical in MariaDB. Using UPPER is preferred because it matches the SQL standard and works in PostgreSQL, DB2, and other databases without modification.

example

SELECT UCASE(country) FROM addresses;

→-- 'UNITED KINGDOM'

◆MariaDB Note

Exact synonym for UPPER().

⇄ vs MySQL: Identical to MySQL.

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

LCASE

MariaDB 5.5+→ 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().

LCASE and LOWER are identical in MariaDB. LOWER is the SQL-standard name and is recognized by all major databases. Prefer it for portability.

example

SELECT LCASE(email) FROM users;

→-- 'alice@example.com'

◆MariaDB Note

Exact synonym for LOWER().

⇄ vs MySQL: Identical to MySQL.

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

REPLACE

MariaDB 5.5+→ 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.

REPLACE inside an UPDATE is the most efficient way to do bulk find-and-replace in a column — no need to fetch rows to the application layer. It's case-sensitive by default; for case-insensitive replacement, combine with LOWER or a collation change.

example

UPDATE articles SET body = REPLACE(body, 'http://', 'https://');

→-- all http links upgraded in-place

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

⇄ vs PostgreSQL: PostgreSQL REPLACE() behaves identically.

REVERSE

MariaDB 5.5+→ 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.

B-tree indexes support prefix queries (LIKE 'abc%') but not suffix queries (LIKE '%xyz'). Store a REVERSE() copy of the column and query LIKE 'zyx%' on it for efficient suffix searching.

example

SELECT * FROM domains WHERE reversed_name LIKE CONCAT(REVERSE('example.com'), '%');

→-- efficient suffix search without full-table scan

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

REPEAT

MariaDB 5.5+→ 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.

REPEAT is handy for generating separator strings, building fixed-width output, or creating test data. Avoid very large repeat counts in production queries as the result is fully materialized in memory.

example

SELECT CONCAT(label, REPEAT('.', 40 - CHAR_LENGTH(label)), value) FROM config;

→-- 'timeout............................30'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

SPACE

MariaDB 5.5+→ 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.

SPACE(n) is cleaner than REPEAT(' ', n) when you need to insert whitespace for formatting. It clearly communicates intent and is slightly more readable in complex CONCAT expressions.

example

SELECT CONCAT(first_name, SPACE(20 - CHAR_LENGTH(first_name)), last_name) FROM users;

→-- 'Alice Smith'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LOCATE

MariaDB 5.5+→ 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.

LOCATE(substr, str, pos) starts searching at position pos, which lets you find the second or Nth occurrence by chaining calls: LOCATE(sub, str, LOCATE(sub, str) + 1).

example

SELECT LOCATE('/', path, LOCATE('/', path) + 1) AS second_slash FROM urls;

→-- position of second '/' in URL

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ 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

MariaDB 5.5+→ 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.

INSTR(str, substr) is the two-argument form that mirrors Oracle's convention, making migrations easier. Use LOCATE when you need a start position; use INSTR for simple 'does this substring exist' checks.

example

SELECT * FROM products WHERE INSTR(tags, 'sale') > 0;

→-- rows where tags contain 'sale'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

POSITION

MariaDB 5.5+→ 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').

POSITION(substr IN str) is the SQL-standard form and is also valid in PostgreSQL. Prefer it in cross-database SQL scripts over LOCATE() to maximize portability.

example

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

→-- position of '@' in each email

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

Common Gotchas

⚠

strpos() returns 0 (not -1 or NULL) when substring not found

strpos() returns 0 for 'not found', not NULL or -1 like many languages. Writing > -1 will match everything.

FIND_IN_SET

MariaDB 5.5+→ 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.

FIND_IN_SET('admin', roles) is safer than LIKE '%admin%' which would also match 'superadmin'. It returns the 1-based position or 0 if not found, and correctly handles list boundaries.

example

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

→-- users with exactly 'admin' in their CSV roles list

◆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

MariaDB 5.5+→ 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.

STRCMP returns -1, 0, or 1, giving you a clear ordering signal suitable for CASE expressions. It respects the column collation, so case sensitivity follows your table definition.

example

SELECT name, STRCMP(name, 'M') AS position FROM customers ORDER BY name;

→-- -1 if before 'M', 0 if equal, 1 if after

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

SUBSTRING_INDEX

MariaDB 5.5+→ 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.

SUBSTRING_INDEX is the most readable way to extract segments from delimited strings. Use positive count for prefix, negative for suffix. Chain two calls to extract a middle segment.

example

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ip, '.', 3), '.', -1) AS third_octet FROM network;

→-- '168' from '192.168.1.1'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

INSERT

MariaDB 5.5+→ 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.

INSERT(str, pos, len, newstr) replaces len characters starting at pos. It's more readable than CONCAT(LEFT(...), newstr, SUBSTRING(...)) when you know the position. Used in data masking to redact credit card middle digits.

example

SELECT INSERT(card_number, 5, 8, '****-****') AS masked FROM payments;

→-- '1234-****-****-5678'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

ELT

MariaDB 5.5+→ 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.

ELT(N, 'a','b','c') returns the Nth string — effectively a small inline array. Useful for converting numeric status codes to labels without a JOIN, but only practical for lists up to ~10 items.

example

SELECT ELT(status, 'Pending', 'Active', 'Closed') AS status_label FROM orders;

→-- 'Active' when status = 2

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

FIELD

MariaDB 5.5+→ 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.

FIELD(value, list...) returns the 1-based position of value in the list, or 0 if not found. Use it in ORDER BY to sort rows in a specific custom order rather than alphabetically or by ID.

example

SELECT * FROM tasks ORDER BY FIELD(priority, 'critical', 'high', 'medium', 'low');

→-- rows sorted critical first, then high, medium, low

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ 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.

FORMAT(1234567.891, 2) returns '1,234,567.89' as a string — perfect for reports but unsuitable for further arithmetic. Never store FORMAT output back into a numeric column.

example

SELECT FORMAT(revenue, 2) AS display_revenue FROM sales;

→-- '1,234,567.89'

◆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

MariaDB 5.5+→ 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.

HEX() converts binary blobs or integers to a safe uppercase hex string. It's the lightest encoding option (2 bytes per input byte) compared to TO_BASE64 (4/3 ratio). Use for checksums, binary ID display, and debugging.

example

SELECT HEX(uuid_col) FROM sessions;

→-- '550E8400E29B41D4A716446655440000'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

UNHEX

MariaDB 5.5+→ 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').

UNHEX is the inverse of HEX. Use it when inserting binary UUIDs or hashes provided as hex strings. Storing UUIDs as BINARY(16) via UNHEX uses 16 bytes vs 36 bytes for the text form.

example

SELECT * FROM sessions WHERE uuid_col = UNHEX('550E8400E29B41D4A716446655440000');

→-- row with matching binary UUID

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

ASCII

MariaDB 5.5+→ 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.

ASCII returns the numeric code of the first character. Values < 32 indicate control characters; values > 127 indicate non-ASCII. Use in a WHERE clause to audit imported data for hidden characters.

example

SELECT * FROM imports WHERE ASCII(SUBSTRING(value, 1, 1)) < 32;

→-- rows starting with a control character

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

CHAR

MariaDB 5.5+→ 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.

CHAR(9) is a tab, CHAR(10) is a newline, CHAR(13) is a carriage return. Using CHAR() instead of literal escape characters makes queries more readable and avoids editor/encoding issues.

example

SELECT REPLACE(description, CHAR(13), '') FROM imports;

→-- carriage returns stripped from description

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

ORD

MariaDB 5.5+→ 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.

ASCII only returns the value of the first byte; ORD returns the full multi-byte code point. For utf8mb4 data, ORD gives the correct Unicode code point while ASCII would return an incorrect partial value.

example

SELECT ORD('é');

→-- 50089 (correct UTF-8 code point, not 195)

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

BIT_LENGTH

MariaDB 5.5+→ 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.

BIT_LENGTH returns the number of bits (8 × bytes). It's rarely needed for string data but is the right function when working with binary protocols or measuring bitfield storage. Under utf8mb4, each character may be 8-32 bits.

example

SELECT BIT_LENGTH(payload) FROM messages WHERE BIT_LENGTH(payload) > 65535 * 8;

→-- messages larger than 64 KB

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

SOUNDEX

MariaDB 5.5+→ 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.

SOUNDEX encodes a string to its phonetic representation, so 'Smith' and 'Smyth' share the same code. Pre-compute and store the SOUNDEX of name columns to run fast phonetic lookups without full-table scans.

example

SELECT * FROM contacts WHERE SOUNDEX(last_name) = SOUNDEX('Thomson');

→-- matches 'Thompson', 'Thomson', 'Thomsen'

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

QUOTE

MariaDB 5.5+→ 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.

QUOTE wraps a string in single quotes and escapes interior quotes and special characters, producing a value safe for embedding in a SQL statement. This is useful in stored procedures that build audit log entries.

example

SELECT CONCAT('INSERT INTO audit VALUES (', QUOTE(user_action), ')') FROM events;

→-- 'INSERT INTO audit VALUES (\'login\')'

◆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

MariaDB 5.5+→ 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.

CHARSET() reveals which character set a string expression uses. Collation mismatches between two columns — e.g., utf8mb4 vs latin1 — prevent index use and can cause 'Illegal mix of collations' errors. Use CHARSET() to spot the problem.

example

SELECT CHARSET(col1), CHARSET(col2) FROM t1 LIMIT 1;

→-- 'utf8mb4', 'latin1' — mismatch identified

◆MariaDB Note

MariaDB/MySQL-specific metadata function, not available in other databases.

⇄ 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

MariaDB 5.5+→ 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.

COERCIBILITY returns a value 0-6 indicating how easily the collation of a string can be converted. Lower values override higher ones in comparisons. When two operands have the same coercibility but different collations, MariaDB raises an error — use this function to identify which side needs an explicit COLLATE clause.

example

SELECT COERCIBILITY(col), COERCIBILITY('literal') FROM t1 LIMIT 1;

→-- 2 (implicit), 4 (coercible)

◆MariaDB Note

MariaDB/MySQL-specific function for collation coercibility inspection.

⇄ vs MySQL: Identical to MySQL.

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

COLLATION

MariaDB 5.5+→ 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.

COLLATION() returns the collation name of a string expression. Use it to confirm that a CONVERT or COLLATE clause took effect, especially after changing a table's character set without recreating indexes.

example

SELECT COLLATION(name), COLLATION(CONVERT(name USING utf8mb4)) FROM products LIMIT 1;

→-- 'latin1_swedish_ci', 'utf8mb4_general_ci'

◆MariaDB Note

MariaDB/MySQL-specific metadata function, not available in other databases.

⇄ 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.

REGEXP_REPLACE lets you do pattern-based substitutions that REPLACE() cannot handle. It's particularly useful for stripping HTML tags, normalizing phone numbers, or collapsing multiple spaces. Available from MariaDB 10.0.5 via PCRE.

example

UPDATE articles SET body = REGEXP_REPLACE(body, '<[^>]+>', '');

→-- HTML tags stripped from body text

◆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.

Common Gotchas

⚠

regexp_replace without 'g' flag only replaces the FIRST match

regexp_replace replaces only the first occurrence by default. The 'g' flag is required for global (all occurrences) replacement.

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.

REGEXP_SUBSTR returns the first substring matching a PCRE pattern. Useful for extracting structured tokens like postal codes, phone numbers, or version strings from unstructured text columns.

example

SELECT REGEXP_SUBSTR(notes, '[0-9]{5}(-[0-9]{4})?') AS zip FROM contacts;

→-- '94103' or '94103-1234'

◆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.

REGEXP_INSTR returns the byte position of the first regex match. Chain it with SUBSTRING to extract the matched region when you need more control than REGEXP_SUBSTR provides — for example, when you need to find overlapping matches.

example

SELECT REGEXP_INSTR(text, 'error|warning') AS issue_pos FROM logs;

→-- position of first 'error' or 'warning' keyword

◆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.

REGEXP_LIKE (or the REGEXP operator) replaces long OR chains of LIKE patterns. The 'i' flag enables case-insensitive matching without changing the column collation. Use it for validation checks in CHECK constraints.

example

SELECT * FROM users WHERE REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}
    
    
    
  

);

→-- rows with syntactically valid email addresses

◆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.

TO_BASE64 produces the standard Base64 encoding with line breaks every 76 characters (per MIME). Use it when you must store binary data in a JSON column or email body. Remember the ~33% size overhead.

example

SELECT TO_BASE64(thumbnail) AS b64 FROM media;

→-- '/9j/4AAQSkZJRgABAQ...'

◆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.

FROM_BASE64 is the inverse of TO_BASE64. Use it to decode externally encoded payloads (e.g., from APIs) before inserting into BLOB columns. It silently returns NULL for strings that are not valid Base64.

example

SELECT LENGTH(FROM_BASE64(encoded_data)) AS decoded_size FROM uploads;

→-- byte size of decoded binary

◆MariaDB Note

Added in MariaDB 10.0.

⇄ vs MySQL: Identical to MySQL.

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

EXPORT_SET

MariaDB 5.5+→ 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.

EXPORT_SET(bits, on, off, sep, bits_count) is the neatest way to render a bitmask as a delimited string of on/off labels. Useful for feature-flag columns, permission masks, or any integer that represents a set of boolean flags.

example

SELECT EXPORT_SET(permissions, 'Y', 'N', ',', 4) AS flags FROM users;

→-- 'Y,N,Y,Y' for permissions = 0b1011

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

MAKE_SET

MariaDB 5.5+→ 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.

MAKE_SET returns only the labels whose corresponding bit is set in the bitmask, joined by a comma by default. It's more concise than EXPORT_SET when you only want to list the active flags rather than showing Y/N for each bit.

example

SELECT MAKE_SET(5, 'read', 'write', 'execute') AS perms;

→-- 'read,execute' (bits 1 and 3 set in 0b101)

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

OCT

MariaDB 5.5+→ 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.

OCT is handy when you store Unix file permissions as integers and need to display them in the familiar octal notation. OCT(493) = '755', OCT(438) = '666'.

example

SELECT OCT(file_permissions) AS octal_perms FROM files;

→-- '755' for permission value 493

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

BIN

MariaDB 5.5+→ 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.

BIN converts an integer to its binary string representation, making it easy to visualize which bits are set in a flags or permissions column. Combine with LPAD to always show a fixed-width binary string.

example

SELECT LPAD(BIN(flags), 8, '0') AS binary_flags FROM settings;

→-- '00001101' for flags = 13

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ 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.

NATURAL_SORT_KEY generates a sort key that orders '10' after '9' instead of before it. This solves the classic problem where ORDER BY version_string produces 1.0, 1.10, 1.2 instead of 1.0, 1.2, 1.10.

example

SELECT version FROM releases ORDER BY NATURAL_SORT_KEY(version);

→-- 1.2, 1.10, 1.20 (natural order)

◆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

MariaDB 5.5+→ 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.

LOAD_FILE reads a file from the server filesystem into a BLOB. It requires FILE privilege and the file must be readable by the MariaDB process. Returns NULL silently if the file doesn't exist — always check the result before inserting.

example

INSERT INTO documents (content) VALUES (LOAD_FILE('/var/data/report.pdf'));

→-- binary PDF content stored in BLOB column

◆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.

SFORMAT is a MariaDB 10.7+ function that applies C-style format specifiers (%s, %d, %f) to build formatted strings. It's cleaner than multiple nested CONCATs for generating log messages or structured output.

example

SELECT SFORMAT('User {} logged in from {}', username, ip_address) FROM logins;

→-- 'User alice logged in from 192.168.1.1'

◆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

MariaDB 10.0+→ 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.

WEIGHT_STRING returns the binary sort key used by a collation, allowing you to see exactly why two strings sort the way they do. It's primarily a diagnostic tool for collation troubleshooting.

example

SELECT HEX(WEIGHT_STRING(name)) FROM users ORDER BY name LIMIT 5;

→-- binary sort keys used by the collation

◆MariaDB Note

Primarily a diagnostic/debugging function for collation analysis.

⇄ vs MySQL: Identical to MySQL.

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

CONVERT

MariaDB 5.5+→ 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.

CONVERT(col USING utf8mb4) recodes a string at query time without altering the stored value. Use it when comparing columns with different character sets to avoid 'Illegal mix of collations' errors.

example

SELECT * FROM t WHERE CONVERT(name USING utf8mb4) = CONVERT(? USING utf8mb4);

→-- comparison succeeds across character set mismatch

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

DECODE

MariaDB 5.5+→ 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.

In MariaDB, DECODE(str, pass) is a synonym for AES_DECRYPT with some Oracle-compatibility behavior. For new code, use AES_DECRYPT explicitly to make the intent clear and to control the encryption mode.

example

SELECT DECODE(encrypted_data, 'secret_key') AS plain FROM vault;

→-- decrypted plaintext string

◆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

MariaDB 5.5+→ 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.

ENCODE(str, pass) is a weak obfuscation alias deprecated in MariaDB 10.3. It should never be used for real security. Replace all ENCODE calls with AES_ENCRYPT(str, key, iv) using a secure AES-256 mode.

example

SELECT AES_ENCRYPT(sensitive_data, 'key', 'iv') FROM records;

→-- binary encrypted blob (replace ENCODE usage)

◆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