String Functions

LENGTH is a trap

A string function operates on a stored sequence of bytes that a character set maps to characters — and the single most common bug is confusing the two. In MySQL, LENGTH() returns the number of bytes and CHAR_LENGTH() (alias CHARACTER_LENGTH()) returns the number of characters. On a utf8mb4 column, LENGTH('café') = 5 because é encodes as the two bytes 0xC3 0xA9, while CHAR_LENGTH('café') = 4. In PostgreSQL the naming is reversed: LENGTH() counts characters and OCTET_LENGTH() counts bytes. Use the character count (CHAR_LENGTH in MySQL, LENGTH in Postgres) for user-facing length limits and validation, or you will reject perfectly valid non-ASCII input.

Traced: the same four strings, byte count vs character count

UTF-8 uses 1 byte for ASCII, 2 for most Latin accents, 3 for CJK, and 4 for emoji and other supplementary characters. Watch how the two counts diverge:

String	Characters	UTF-8 bytes	MySQL LENGTH (bytes)	MySQL CHAR_LENGTH (chars)	PG LENGTH (chars)	PG OCTET_LENGTH (bytes)
'SQL'	3	3	3	3	3	3
'café'	4	5	5	4	4	5
'日本'	2	6	6	2	2	6
'😀'	1	4	4	1	1	4

The all-ASCII 'SQL' is the only row where every column agrees — which is exactly why a bug here survives all your English test data and then fails in production on the first accented name or emoji. A VARCHAR(4) column stores 'café' fine (length limits are in characters), but a byte-based length check would wrongly flag it as 5.

Indexing is 1-based, and functions have dialect quirks

SQL string positions are 1-based, not 0-based like most programming languages. SUBSTRING('SQL Functions', 1, 3) = 'SQL' — position 1 is the first character. Finding a substring returns its 1-based position, or 0 when not found:

-- All 1-based; the "not found" sentinel is 0, not -1 or NULL
POSITION('World' IN 'Hello World')   -- 7   (SQL-standard form, portable)
LOCATE('World', 'Hello World')       -- 7   (MySQL: needle, haystack)
INSTR('Hello World', 'World')        -- 7   (MySQL/Oracle: haystack, needle)  <-- args SWAPPED
POSITION('xyz' IN 'Hello World')     -- 0   (not found)

LOCATE and INSTR give the same answer but take their arguments in the opposite order — a classic silent bug when porting queries. Prefer the standard POSITION(needle IN haystack). Other dialect splits: MySQL/Postgres spell substring extraction as both SUBSTRING and SUBSTR; Oracle uses SUBSTR.

CONCAT vs || and the NULL surprise

Concatenation is where NULL handling silently differs, and the rule is not uniform — verify per engine:

Engine / operator	'a' ‖ NULL ‖ 'b' behavior
MySQL CONCAT('a', NULL, 'b')	→ NULL (any NULL argument poisons the whole result)
MySQL CONCAT_WS('-', 'a', NULL, 'b')	→ 'a-b' (CONCAT_WS skips NULL arguments)
PostgreSQL CONCAT('a', NULL, 'b')	→ 'ab' (the CONCAT function ignores NULLs)
PostgreSQL 'a' || NULL || 'b'	→ NULL (the || operator yields NULL on any NULL operand)
Oracle 'a' || NULL || 'b'	→ 'ab' (Oracle treats NULL as the empty string)

So "does a NULL disappear or poison the row?" depends on both the engine and whether you used the function or the operator. When in doubt, wrap nullable operands in COALESCE(col, '') to make the intent explicit and portable.

Trailing spaces: CHAR vs VARCHAR

CHAR(n) is a fixed-width type — stored values are right-padded with spaces to length n; VARCHAR(n) stores only what you put in. The gotcha is comparison. Under a PAD SPACE collation (the historical default in MySQL and the SQL-standard behavior for CHAR), trailing spaces are ignored in = comparisons, so 'abc' = 'abc ' is true. Under a NO PAD collation (e.g. MySQL's utf8mb4_0900_ai_ci), they are significant. Either way, LIKE treats trailing spaces literally — 'abc ' LIKE 'abc' is false. Because this is collation- and mode-dependent, normalize with TRIM() on input rather than relying on padding semantics you may not control.

Case, collation, and sargability

Case-insensitivity is a property of the collation, not of UPPER/LOWER. MySQL's default *_ci collations already compare case-insensitively, so WHERE email = 'A@x.com' matches 'a@x.com' for free. PostgreSQL is case-sensitive by default; reach for a case-insensitive collation, the citext type, or a functional index. The anti-pattern to avoid:

-- NON-SARGABLE: the function wraps the column, so a plain index on email is unusable
WHERE UPPER(email) = UPPER(:input)     -- forces a full scan / every-row evaluation

-- SARGABLE options:
--   (a) use a case-insensitive collation and compare directly:  WHERE email = :input
--   (b) build a matching functional/expression index:
--       Postgres: CREATE INDEX ON t (UPPER(email))
--       MySQL 8 : CREATE INDEX idx ON t ((UPPER(email)))   -- expression needs an extra ()
--   (c) store a lowercased shadow column and index that

-- Leading wildcard also kills the index:
WHERE name LIKE '%son'   -- NON-SARGABLE: no known prefix, B-tree can't seek -> scan
WHERE name LIKE 'John%'  -- SARGABLE: prefix 'John' lets the B-tree range-seek

Wrapping a column in any function (UPPER, SUBSTRING, TRIM, …) inside a WHERE predicate defeats an ordinary index on that column, because the index stores the raw values, not the transformed ones. A leading-wildcard LIKE has the same problem: with no anchored prefix there is no range to seek. (In PostgreSQL, even a prefix LIKE needs text_pattern_ops or a C-locale index to be used in non-C locales — another collation-dependent hedge.)

A quick tour of the everyday functions

Function	Does	Example → result
TRIM / LTRIM / RTRIM	strip spaces (both / left / right)	TRIM(' SQL ') → 'SQL'
LPAD / RPAD	pad to a width with a fill string	LPAD('7', 3, '0') → '007'
REPLACE	substitute all occurrences	REPLACE('a.b.c', '.', '-') → 'a-b-c'
LEFT / RIGHT	first / last N characters	RIGHT('MySQL', 3) → 'SQL'
SUBSTRING_INDEX (MySQL)	split on the Nth delimiter	SUBSTRING_INDEX('a@b.com','@',-1) → 'b.com'
UPPER / LOWER	case fold (per collation rules)	UPPER('café') → 'CAFÉ'

Selection & trade-offs

• Validate on characters, store as bytes. Use CHAR_LENGTH (MySQL) / LENGTH (Postgres) for "max 280 characters"; use the byte length only when you genuinely care about storage or wire size. Standardize on utf8mb4 in MySQL — the older utf8 (alias utf8mb3) is only 3 bytes and cannot store emoji or many CJK supplementary characters.
• Normalize once, on the way in. Prefer trimming, case-folding, and Unicode-normalizing at write time (or in the app) so reads stay simple and indexable, rather than repeatedly wrapping columns in functions at query time.
• Do heavy string work in the app, not the hot query path. SQL string functions are perfect for projection and light transforms, but a predicate that transforms a column can't use an index. If you must match on a transformed value, materialize it (functional index or shadow column) so the transform is precomputed and seekable.
• Portability costs judgment. If the query must run on more than one engine, favor SQL-standard forms (POSITION(x IN y), SUBSTRING(s FROM a FOR b), explicit COALESCE around concatenation) over dialect-specific spellings with swapped arguments or divergent NULL rules.

Takeaways

• MySQL: LENGTH = bytes, CHAR_LENGTH = characters. Postgres: LENGTH = characters, OCTET_LENGTH = bytes. They only agree on pure ASCII.
• Positions are 1-based, "not found" is 0; LOCATE(needle,haystack) and INSTR(haystack,needle) swap arguments — prefer standard POSITION.
• NULL in concatenation: MySQL CONCAT poisons to NULL (but CONCAT_WS skips), Postgres CONCAT ignores NULL while || poisons, Oracle || treats NULL as ''. Use COALESCE to be sure.
• Case-insensitivity comes from the collation, not UPPER(); wrapping a column in a function — or a leading-wildcard LIKE — is non-sargable and forces a scan.

---

Re-authored and deepened for this guide; bytes-vs-characters diagram hand-authored as SVG. Sources: MySQL 8.0 and PostgreSQL 16 reference manuals (string functions, character sets & collations), the SQL standard's PAD SPACE/NO PAD rules, and the Unicode/UTF-8 encoding spec. Replaces the earlier page, which incorrectly stated MySQL LENGTH() counts characters. See also: Indexes in Practice, How a Query Executes.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — String Functions

Why this concept exists (judgment layer)

Bytes vs characters, CONCAT NULL poisoning, and non-sargable UPPER(col) are cross-engine landmines. Unicode production data exposes English-only tests immediately.

Mental model (install this intuition)

MySQL LENGTH=bytes, CHAR_LENGTH=chars; Postgres LENGTH=chars, OCTET_LENGTH=bytes. Positions are 1-based. Function on column in WHERE usually kills B-tree seek. Normalize on write; keep predicates sargable.

Worked example with numbers or traced steps

'café' utf8mb4: CHAR_LENGTH=4, MySQL LENGTH=5 (é = C3 A9)
'😀': 1 char, 4 bytes
MySQL CONCAT('a',NULL,'b') → NULL; CONCAT_WS skips NULL
PG CONCAT ignores NULL; PG || poisons with NULL
WHERE UPPER(email)=…  non-sargable without functional index
LIKE '%son' scan; LIKE 'John%' prefix range

When NOT to use / named alternative

Do not validate user-facing max length with MySQL LENGTH on utf8mb4 (rejects valid accents). Do not do heavy string transform in hot WHERE — materialize/index expression or normalize at ingest. Prefer app-layer complex parsing over SQL string acrobatics.

Failure mode & ops fingerprint

Fingerprint: emoji insert fails on MySQL utf8 (mb3); emails not found because case fold wrapped column and index unused; CONCAT NULL blanks entire display name in MySQL.

Hostile-panel drills (defend the decision)

Q1. MySQL LENGTH('日本') vs CHAR_LENGTH?
Model answer: LENGTH=6 bytes (3+3); CHAR_LENGTH=2 characters.

Q2. Make case-insensitive email match sargable on Postgres.
Model answer: citext / case-insensitive collation, or functional index on LOWER(email) and query LOWER(:in).

Q3. LOCATE vs INSTR argument order?
Model answer: LOCATE(needle, haystack) vs INSTR(haystack, needle) — swapped; prefer POSITION(needle IN haystack).

I just read a lesson on **String Functions** (Databases) and want to truly understand it. Explain String Functions from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **String Functions** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **String Functions** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **String Functions** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.