Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 40 ยท Functions and Window Functions

String Functions in MariaDB

Learn MariaDB string functions: CONCAT, SUBSTRING, LEFT, LPAD, REPLACE, LOCATE, TRIM and more, plus how to update or replace part of a string stored in a column.

Text is the messiest data you will store: names with stray spaces, codes that need padding, phone numbers in five formats. MariaDB ships with dozens of built-in string functions so you can clean, search and reshape text inside the query instead of in application code.

This lesson covers the functions you will use every week, how to replace part of a string in existing rows with UPDATE, and the traps (1-based positions, NULL propagation, collations). The runnable editors use the portable subset that the in-browser engine understands; MariaDB-only functions are shown in read-only blocks and labelled.

The editors on this site run a portable SQL engine, not MariaDB itself. Where MariaDB spells a function differently (CONCAT instead of the || operator, SUBSTRING instead of SUBSTR), the lesson shows the MariaDB form first and a runnable equivalent next to it.

Joining and measuring text

CONCAT(a, b, ...) joins values. If any argument is NULL the whole result is NULL, which surprises many beginners. CONCAT_WS(separator, a, b, ...) ("with separator") skips NULL arguments and puts the separator only between the values that remain.

SQL
SELECT CONCAT(first_name, ' ', last_name)            AS full_name,
       CONCAT_WS(', ', last_name, first_name, title) AS listing,
       CHAR_LENGTH(last_name)                        AS characters,
       LENGTH('รฉ')                                   AS bytes
FROM employees;

CHAR_LENGTH() counts characters; LENGTH() counts bytes. In a utf8mb4 column the letter รฉ is one character but two bytes, so use CHAR_LENGTH for validation rules such as "at most 30 characters".

The portable engine concatenates with ||:

SQL Build full names ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Slicing: SUBSTRING, LEFT, RIGHT, LOCATE

Positions in SQL strings start at 1, not 0.

SQL
SELECT SUBSTRING('MariaDB Server', 1, 7);   -- 'MariaDB'
SELECT SUBSTRING('MariaDB Server', 9);      -- 'Server'
SELECT LEFT('2026-03-15', 4);               -- '2026'
SELECT RIGHT('2026-03-15', 2);              -- '15'
SELECT LOCATE('@', '[email protected]');      -- 4  (0 means "not found")
SELECT SUBSTRING_INDEX('[email protected]', '@', -1);  -- 'example.com'

SUBSTRING_INDEX(str, delim, count) is a MariaDB (and MySQL) favorite: a positive count returns everything before the count-th delimiter, a negative one everything after the count-th delimiter counted from the right. It is the quickest way to split an e-mail address or a file path without regular expressions.

Portable stand-in using INSTR (the same idea as LOCATE, with the arguments reversed):

SQL Split an e-mail address

Press Run (or Ctrl + Enter) to see the result here.

Padding, trimming and case

SQL
SELECT LPAD(7, 4, '0');             -- '0007'   (fixed-width codes)
SELECT RPAD('ab', 5, '.');          -- 'ab...'
SELECT TRIM('   hello  ');          -- 'hello'
SELECT TRIM(LEADING '0' FROM '00042');  -- '42'
SELECT UPPER('maria'), LOWER('DB'), REVERSE('abc'), REPEAT('ab', 3);

LPAD also truncates: LPAD('abcdef', 4, '0') returns 'abcd'. Remember that when padding values that may be longer than the width.

Replacing text

REPLACE(str, from, to) swaps every occurrence of a substring. INSERT(str, pos, len, newstr) overwrites a slice by position.

SQL
SELECT REPLACE('2026/03/15', '/', '-');       -- '2026-03-15'
SELECT INSERT('0612345678', 4, 3, '***');     -- '061***5678'
SELECT REGEXP_REPLACE('Tel: 06-12 34', '[^0-9]', '');  -- '061234'

REGEXP_REPLACE (MariaDB 10.0.5 and later) uses PCRE, so \d and lookaheads work. When a simple REPLACE is enough, prefer it: it is faster and easier to read.

Updating part of a string in stored rows

To fix data in place, combine UPDATE with a string function. The change is applied to every row that matches the WHERE, so preview it with a SELECT first.

SQL
-- Preview
SELECT id, sku, REPLACE(sku, 'OLD-', 'NEW-') AS new_sku
FROM products
WHERE sku LIKE 'OLD-%';

-- Apply
UPDATE products
SET sku = REPLACE(sku, 'OLD-', 'NEW-')
WHERE sku LIKE 'OLD-%';

The WHERE clause avoids rewriting rows that would not change. After the update, ROW_COUNT() tells you how many rows were really changed.

SQL Replace part of a string with UPDATE

Press Run (or Ctrl + Enter) to see the result here.

Searching with LIKE, REGEXP and collations

LIKE uses % (any run) and _ (one character). Case sensitivity depends on the column collation: the default collations of recent MariaDB versions are case- and accent-insensitive, so 'ana' LIKE 'ANA' is true. Add COLLATE utf8mb4_bin to force an exact comparison.

SQL
SELECT name FROM customers WHERE name LIKE 'A%';
SELECT name FROM customers WHERE name REGEXP '^[A-C].*son$';
SELECT 'Ana' = 'ANA' COLLATE utf8mb4_bin;   -- 0

Also useful: FIELD(x, 'a','b','c') (position in a list, handy in ORDER BY), FORMAT(1234567.891, 2) ('1,234,567.89'), QUOTE(str) which escapes a string for safe literal use, and SOUNDEX() for rough "sounds like" matching.

Common mistakes

  • Using + to join strings. In MariaDB 'a' + 'b' is numeric addition and gives 0. Use CONCAT.
  • Forgetting that CONCAT returns NULL if any argument is NULL. Wrap nullable columns in COALESCE(col, '') or use CONCAT_WS.
  • Wrapping an indexed column in a function in WHERE (WHERE LOWER(name) = 'ana'). That prevents the index from being used; see explain and index design.
  • Counting length with LENGTH on multibyte text.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does CONCAT('a', NULL, 'b') return in MariaDB?

    CONCAT returns NULL when any argument is NULL. CONCAT_WS skips NULL arguments instead.
  2. 2. Which function should you use to count the characters of a UTF-8 string?

    LENGTH returns bytes, so multibyte characters count more than once. CHAR_LENGTH counts characters.
  3. 3. What does SUBSTRING('MariaDB', 1, 5) return?

    Positions start at 1, and the third argument is the length, so five characters starting at the first.
  4. 4. Why should an UPDATE that uses REPLACE usually have a WHERE clause?

    Filtering keeps the update small, cheaper and easy to preview with the same condition in a SELECT.

Summary

  • CONCAT, CONCAT_WS, SUBSTRING, LEFT, RIGHT, LOCATE, LPAD, TRIM, REPLACE cover most text work; positions start at 1.
  • NULL makes CONCAT return NULL; CONCAT_WS is NULL-tolerant.
  • Use UPDATE ... SET col = REPLACE(col, ...) WHERE ... to fix text in place, previewing with SELECT first.
  • Collation decides case and accent sensitivity; CHAR_LENGTH counts characters, LENGTH bytes.

Next: Numeric and Date Functions