String Functions That Earn Their Keep

~12 min · sql, strings, functions

Level 0Scout

0 XP0/80 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The dozen you'll actually call

SQLite ships a small but practical set of string functions. The keepers:

length(s) — character count for TEXT, byte count for BLOB.
upper(s), lower(s) — ASCII case only by default.
trim(s), ltrim(s), rtrim(s) — strip whitespace (or specified chars).
substr(s, start, len) — 1-indexed slicing.
replace(s, old, new) — substring replace.
instr(s, sub) — 1-indexed position of sub in s, or 0 if not found.
printf('%s says %s', a, b) — C-style formatting.
|| — concatenation operator.
group_concat(col, sep) — aggregate concatenate.
hex(blob), quote(s) — for inspecting binary or quoting safely.

Warning: upper / lower only work on ASCII A–Z by default. upper('한글') returns '한글' unchanged. For Unicode-correct case folding you need to load the ICU extension (rarely worth it for application code; do casing in Python instead).

Code

String functions in action·sql

SELECT email,
       length(email)                 AS n,
       substr(email, 1, instr(email, '@') - 1) AS local_part,
       substr(email, instr(email, '@') + 1)   AS domain,
       printf('%s -> %s', username, email) AS pretty
FROM users;

External links

Exercise

Take a column of free-form text from any of your tables and write five derived columns: length, lowercased, trimmed, the first word (everything before the first space), and a printf-formatted display string. Confirm each works on edge cases (empty string, no spaces, leading whitespace, Unicode).

Progress

Progress is local-only — sign in to sync across devices.

← PreviousCASE WHEN — Conditional Expressions Next →Date and Time Functions

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.