MariaDB Tutorial / Join, Extract, Replace, and Measure Strings

String functions join, extract, replace or measure text to produce query results. Choose CONCAT, SUBSTRING, REPLACE and CHAR_LENGTH according to the task, and distinguish NULL handling and character counts from byte counts before changing the meaning of the original data.

Choose a function for the transformation

Combining a name and category, extracting part of a code and removing telephone separators are different transformations. Calling a string function in SELECT calculates an output value; it does not automatically update a stored name. Decide what should remain original data and what belongs only in a display. Check the input column’s storage rules in Choose String Types: CHAR, VARCHAR, and TEXT and the missing-value semantics in Compare NULL Correctly with IS NULL and COALESCE when transforming text.

Check the MariaDB documentation for the syntax and conditions used here: mariadb string functions guide, substring.

Build display labels from the practice data

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_19_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_19_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p19_price CHECK (price >= 0),
  CONSTRAINT ck_p19_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_19_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

The notes contain both NULL and empty strings. Compare the NULL and empty-string examples when choosing a missing-value display policy.

SELECT product_id, CONCAT(product_name,' / ',category) AS label,
 CONCAT_WS(' / ',product_name,note) AS note_label
FROM products_19_lab ORDER BY product_id;

For product 1, label combines the notebook and stationery strings with a slash, while note_label contains only its name because CONCAT_WS skips the NULL note. Product 2 has an empty note, so its separator remains. Do not rewrite empty notes as NULL merely to remove an unwanted separator; first decide the intended display rule.

Extract by character position and replace separators

SELECT SUBSTRING('MariaDB',1,5), SUBSTRING('MariaDB',-2);
SELECT REPLACE('010-1234-5678','-','') AS normalized_phone;

The results are Maria, DB and 01012345678. SUBSTRING starts at position 1, not zero, and a negative position counts from the end. Its positions refer to characters, making it suitable for multibyte strings too. Establish that a business code really has a fixed positional structure before cutting it apart.

REPLACE calculates a substituted string here. Converting the resulting telephone string to a number could lose a leading zero, country-code information or extensions. Normalizing an identifier requires more than deleting punctuation.

Compare characters with UTF-8 bytes

SELECT CHAR_LENGTH('한글'), LENGTH('한글'),
 CHAR_LENGTH('🙂'), LENGTH('🙂');

With utf8mb4 input and connection handling, the Korean sample contains two characters and six bytes; the emoji is one character and four bytes. A visually single symbol can also consist of multiple code points, so CHAR_LENGTH does not universally count user-perceived glyph clusters.

A 100-character input rule and a 100-byte storage or network limit are different requirements. The string-type and length discussion explains the storage context. Arbitrarily cutting UTF-8 bytes can produce an invalid sequence.

Keep NULL, empty text and trimming policies separate

SELECT CONCAT('노트',NULL),
 CONCAT_WS(' / ','노트',NULL,'');
SELECT CONCAT('[',TRIM('  노트  '),']');

The first CONCAT result is NULL. CONCAT_WS retains the empty string and therefore a trailing separator. TRIM removes the ordinary surrounding spaces in this example, leaving the Korean notebook name inside brackets. It does not universally remove every Unicode whitespace character or interior space. Names and passwords may contain meaningful spaces.

Collation affects comparisons involving case, accents and trailing spaces. If those distinctions matter, inspect the type and collation together rather than assume a different string function fixes the comparison policy.

Compare original and transformed values before updating

Display the primary key, original value and calculated value side by side. For unexpectedly short output, check position and length; for NULL output, check each argument; for garbled Korean text, check file and connection encoding. Validate a transformation with SELECT and use a recoverable change procedure before applying it through UPDATE.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_19_lab;