Which String Functions Come Up Most?

Six families cover almost everything asked. Length and position: LENGTH for character count, INSTR (or POSITION, or CHARINDEX) to find where a substring starts. Slicing: SUBSTR (or SUBSTRING) with a start position and an optional length. Case: UPPER and LOWER. Whitespace: TRIM, plus LTRIM and RTRIM when you want to be specific. Replacement and concatenation: REPLACE, and the || operator — note that CONCAT is not standard, and MySQL's CONCAT() and PostgreSQL's CONCAT() are recent additions that older versions lack.

Two more that get asked: COALESCE for a fallback value, and LIKE for pattern matching with % and _. And on PostgreSQL specifically, split_part and string_agg, which have no direct equivalent elsewhere.

The thing that separates a strong answer from a memorised one is knowing which functions can use an index. A function that wraps the indexed column — LOWER(name), TRIM(email), SUBSTR(description, 1, 10) — makes the predicate non-sargable, so the engine must read every row and compute the function. If you need case-insensitive matching frequently, the fix is a functional index on the lowercased column rather than wrapping the column in every query. In MySQL, a case-insensitive collation on the column itself achieves the same thing without touching the query.

One more practical point: string functions operate on characters, not bytes, so LENGTH of a multi-byte character counts one, not two. But SUBSTRING_INDEX and the various POSITION functions count bytes, which is why slicing in the middle of a multi-byte character can produce invalid text. The functions that respect characters are the ones you usually want for user-facing data.

TRIM removes leading and trailing whitespace in one call, and UPPER or LOWER normalises the case. SQLite has no INITCAP, so a mixed-case display name needs the first character separated from the rest.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(160) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, '  ada LOVELACE  '),
  (2, 'grace hopper'),
  (3, 'ALAN turing'),
  (4, 'Katherine  johnson');

SELECT id,
       TRIM(name) AS trimmed_name,
       UPPER(TRIM(name)) AS shouty_name,
       REPLACE(TRIM(name), '  ', ' ') AS single_spaced
FROM customers
ORDER BY id;

Four rows come back. Row 1 goes from " ada LOVELACE " to "ada LOVELACE" once trimmed, and row 4's double space between the two names collapses to one. The shouty_name column uppercases everything, which is usually not what a display name wants but is exactly right for a search key or a comparison column.

Two limits worth knowing. SQLite and MySQL have no INITCAP, so a properly title-cased name is not a one-liner — the first-letter trick only affects the very first character of the whole string, giving "Ada lovelace" rather than "Ada Lovelace". For real title casing you need to capitalise the first letter of every word, which in SQLite means either a recursive CTE that walks the string word by word or a user-defined function. On PostgreSQL, the extension initcap does it directly. And the internal-whitespace collapse above is a single REPLACE pass, so a name with three consecutive spaces still needs two passes.

The bigger point is where to do this work. Cleaning in the query means every consumer has to repeat the expression, and the function call stops the column from using its index. Normalising on write — trimming and storing a canonical form in the database — costs one pass at insert time and keeps the index usable forever. Most teams end up doing both: canonicalise on write, and be forgiving in the query for rows that predate the change.

The portable way to find the domain is to locate the at-sign with INSTR and take everything after it with SUBSTR. There is no standard split function, so this two-step is the approach that works everywhere.

CREATE TABLE subscribers (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL
);

INSERT INTO subscribers (id, email) VALUES
  (1, '[email protected]'),
  (2, '[email protected]'),
  (3, '[email protected]'),
  (4, '[email protected]');

SELECT id,
       LOWER(TRIM(email)) AS normalised_email,
       LOWER(SUBSTR(TRIM(email), INSTR(TRIM(email), '@') + 1)) AS domain
FROM subscribers
ORDER BY id;

Four rows come back. Row 1 becomes [email protected] with the domain example.com, row 2 keeps example.com, row 3 has sub.example.co.uk — note that the domain is everything after the last at-sign, so a subdomain is preserved — and row 4 has example.org.

How it works: INSTR returns the 1-based position of the at-sign, and adding one gives the first character after it, which SUBSTR then uses as the start of the domain. Wrapping the trimmed value in both calls matters, because a stray leading space would otherwise shift the position and produce a domain with a space in it.

Three caveats. If an address has no at-sign, INSTR returns 0 and SUBSTR starts at position 1, so the whole address becomes the domain — guard it with a CASE that checks INSTR is greater than zero. If an address has several at-signs, this takes everything after the first one, whereas MySQL's SUBSTRING_INDEX would take everything after the last; decide which behaviour you want. And on PostgreSQL, split_part(email, '@', 2) is far clearer than the SUBSTR arithmetic.

Finally, normalise on write rather than on read. Lowercasing in every query means the function wraps the column and defeats the index on email, and it means two spellings of the same address can both be inserted. Storing one canonical form is the fix that keeps lookups fast.