String Functions

String Functions

String positions start at 1. LENGTH() counts bytes and CHAR_LENGTH() characters, which differ once text leaves ASCII. The workhorses are CONCAT(), CONCAT_WS() (with a separator, skipping NULLs), LOWER(), UPPER(), TRIM(), LPAD(), LEFT(), SUBSTRING(), SUBSTRING_INDEX(), LOCATE(), REPLACE() and FIELD(). The first query builds a URL slug from each title.

Slugs from product titles, and labels built from a customer rowSQL

SELECT title,
       TRIM(BOTH '-' FROM REGEXP_REPLACE(LOWER(title), '[^a-z0-9]+', '-')) AS slug
FROM products WHERE category_id = 2;
SELECT CONCAT_WS(' / ', name, NULL, country) AS label,
       SUBSTRING_INDEX(email, '@', -1) AS domain, CONCAT('C-', LPAD(id, 5, '0')) AS acct,
       UPPER(LEFT(name, 3)) AS code, CONCAT(name, NULL) AS cat,
       LENGTH('Café') AS bytes, CHAR_LENGTH('Café') AS chars
FROM customers WHERE id = 3;

Output

+----------------------------+----------------------------+
| title                      | slug                       |
+----------------------------+----------------------------+
| Modern PHP in Practice     | modern-php-in-practice     |
| Laravel From the Ground Up | laravel-from-the-ground-up |
| The Linux Server Handbook  | the-linux-server-handbook  |
+----------------------------+----------------------------+
+---------------+-------------+---------+------+------+-------+-------+
| label         | domain      | acct    | code | cat  | bytes | chars |
+---------------+-------------+---------+------+------+-------+-------+
| Chen Wei / MY | example.com | C-00003 | CHE  | NULL |     5 |     4 |
+---------------+-------------+---------+------+------+-------+-------+

The slug lowercases, turns each run of other characters into one hyphen, and trims hyphens from the ends. It would drop the "é" of "Café", so transliterate accented titles in PHP (Multibyte Strings) or use Laravel 2,157 's Str::slug(). VARCHAR(n) counts characters, so validate with CHAR_LENGTH(). CONCAT() returns NULL if any part is NULL, which is why optional parts belong in CONCAT_WS(). ORDER BY FIELD(status, 'pending', 'paid', 'shipped') sorts in a custom order.

MENU