String Functions

Lesson 0008 — Collation, CONCAT, LOWER/UPPER, TRIM, LENGTH, SUBSTRING & LIKE — ~16 min read

Text is never clean. Different cases, accents, extra spaces, and mixed formats break comparisons. String functions normalize and slice text — but collation decides what “equal” even means.

In real databases, text data is rarely consistent:

• Different cases — Apple, apple, APPLE
• Accents — café vs cafe
• Extra spaces
• Mixed formats

String functions help normalize, format, and filter text so comparisons and reports behave correctly. But first — how SQL compares strings.

In SQL, a string is stored with collation rules that decide how text is compared, sorted, and matched. Two strings that look different may be treated as equal.

Sample Table — Collation Matters

CREATE TABLE sales (
  item     VARCHAR(30) NOT NULL, quantity INT DEFAULT NULL
) ENGINE=InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE sales (
  item     VARCHAR(30) NOT NULL, quantity INT DEFAULT NULL
);
-- pgsql: collation per column/database via CREATE COLLATION; default is often case-sensitive

This table's collation is utf8mb4_0900_ai_ci — accent-insensitive, case-insensitive.

What is Collation?

Rules for comparing and sorting strings:

1) CONCAT() vs CONCAT_WS()

• CONCAT(a,b,c…) joins strings as-is
• CONCAT_WS(sep, a,b,c…) joins with a separator (WS = with separator) and skips NULL values

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

SELECT 'Hello' || ' ' || 'Raj';

Output: Hello Raj

SELECT CONCAT('Hello', NULL, 'Raj');

SELECT 'Hello' || NULL || 'Raj';

Output: NULL — CONCAT returns NULL if any argument is NULL (same for ||).

SELECT CONCAT_WS(' ', 'Hello', NULL, 'Raj');

SELECT CONCAT_WS(' ', 'Hello', NULL, 'Raj');

Output: Hello Raj — CONCAT_WS ignores NULL.

SELECT CONCAT_WS('. ', NULL, 'Hi', 'Raj');

SELECT CONCAT_WS('. ', NULL, 'Hi', 'Raj');

Output: Hi. Raj — separator applied only between real values.

Tip: Use CONCAT when no NULL expected; use CONCAT_WS when NULLs may exist.

2) LOWER() / UPPER()

• LOWER() → lowercase
• UPPER() → uppercase

SELECT LOWER('RAjstriveR');

SELECT LOWER('RAjstriveR');

Output: rajstriver

SELECT UPPER('RAjstriveR');

SELECT UPPER('RAjstriveR');

Output: RAJSTRIVER

Useful for standardizing text for display or case-insensitive comparisons.

3) TRIM() / LTRIM() / RTRIM()

• TRIM() — both sides
• LTRIM() — left only
• RTRIM() — right only

SELECT TRIM('  RAJ  ');

SELECT TRIM('  RAJ  ');

Output: RAJ

SELECT LTRIM(' RAJ ');

SELECT LTRIM(' RAJ ');

Output: RAJ␣ (trailing space remains)

SELECT RTRIM(' RAJ ');

SELECT RTRIM(' RAJ ');

Output: ␣RAJ (leading space remains)

Common use: cleaning user input or CSV imports.

4) LENGTH() vs CHAR_LENGTH()

• LENGTH() → number of bytes
• CHAR_LENGTH() → number of characters

SELECT LENGTH('RAJ');

SELECT LENGTH('RAJ');

Output: 3

SELECT LENGTH('é');

SELECT LENGTH('é');

Output: 2 (MySQL) / 1 (pgsql — LENGTH counts chars in PG). In MySQL, é is 2 bytes in utf8mb4.

SELECT LENGTH('😀');

SELECT LENGTH('😀');

Output: 4 (MySQL bytes) / 1 (pgsql chars)

SELECT CHAR_LENGTH('RAJ');

SELECT CHAR_LENGTH('RAJ');

Output: 3

SELECT CHAR_LENGTH('é');

SELECT CHAR_LENGTH('é');

Output: 1

SELECT CHAR_LENGTH('😀');

SELECT CHAR_LENGTH('😀');

Output: 1

Use CHAR_LENGTH for user-visible characters, LENGTH for byte-level checks (MySQL).

5) LEFT() / RIGHT() / SUBSTRING()

• LEFT(str, n) — first n chars
• RIGHT(str, n) — last n chars
• SUBSTRING(str, start, len) — slice (MySQL 1-based)

SELECT LEFT('plus_user', 3);

SELECT LEFT('plus_user', 3);

Output: plu

SELECT RIGHT('plus_user', 4);

SELECT RIGHT('plus_user', 4);

Output: user

SELECT SUBSTRING('plus_user', 3);

SELECT SUBSTRING('plus_user' FROM 3);

Output: us_user (from 3rd char onward)

SELECT SUBSTRING('plus_user', 3, 2);

SELECT SUBSTRING('plus_user' FROM 3 FOR 2);

Output: us

6) LOCATE() / INSTR() — Finding Substrings

• LOCATE(substr, str) — MySQL
• INSTR(str, substr) — MySQL alternative
• POSITION(substr IN str) / STRPOS(str, substr) — PostgreSQL

1-based index; returns 0 if not found.

SELECT LOCATE('@', 'Raj@gmail.com');

SELECT POSITION('@' IN 'Raj@gmail.com');

Output: 4

SELECT INSTR('Raj@gmail.com', '@');

SELECT STRPOS('Raj@gmail.com', '@');

Output: 4

SELECT INSTR('Raj@gmail.com', '7');

SELECT STRPOS('Raj@gmail.com', '7');

Output: 0 (not found)

7) REPLACE() — Substituting Text

Replaces all occurrences of a substring.

SELECT REPLACE('a_b_c', '_', '-');

SELECT REPLACE('a_b_c', '_', '-');

Output: a-b-c

8) LIKE — Pattern Matching Recap

• % = any length (0+)
• _ = exactly one character

SELECT 'plus_user' LIKE 'plus%';

SELECT 'plus_user' LIKE 'plus%';

Output: 1 (true)

SELECT 'plus1user' LIKE 'plus_us_r';

SELECT 'plus1user' LIKE 'plus_us_r';

Output: 1 — _ matches 1

SELECT 'plus1user' LIKE 'plus\_user';

SELECT 'plus1user' LIKE 'plus\_user';

Output: 0 — escaped \_ is literal _, not wildcard.

Note: Escaping \_/\% depends on SQL mode.

Notes

Primary sources: MySQL String Functions · Postgres String Functions · MySQL Collation. Ask me anything that's unclear.

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.