TopolearnData arrives with stray spaces, inconsistent case and combined fields; fixing it in SQL avoids round trips.
Every database ships dozens of string functions. The core set, with PostgreSQL names:
length(s),lower(s),upper(s).trim(s)removes surrounding spaces;ltrim,rtrimremove one side.substring(s FROM 2 FOR 3)orsubstr(s, 2, 3)extracts characters; positions start at 1, not 0.position('@' IN email)finds a substring (0 if absent).replace(s, 'from', 'to')replaces every occurrence.split_part(email, '@', 2)takes the n-th field of a delimited string.concat_ws(', ', a, b, c)joins with a separator and, unlike||, skips NULL arguments.left(s, n),right(s, n),lpad(s, n, '0').
For anything beyond LIKE, PostgreSQL supports POSIX regular expressions: email ~ '^[a-z]+@' tests a match (~* is case-insensitive) and regexp_replace(phone, '[^0-9]', '', 'g') strips non-digits.
string_agg(name, ', ' ORDER BY name) is an aggregate that concatenates values across rows; MySQL calls it GROUP_CONCAT, Oracle and the standard LISTAGG.
Dialect names vary more than behaviour: SQL Server uses LEN (which ignores trailing spaces) and CHARINDEX; MySQL's LENGTH counts bytes, so use CHAR_LENGTH for characters in UTF-8 text. PostgreSQL's length counts characters.
Applying a function to a column in WHERE, such as WHERE lower(email) = 'ana@example.com', normally prevents a plain index on email from being used. Either store data normalised or create an index on the expression itself, as the index lessons show.
Example
SELECT email,
split_part(email, '@', 1) AS local_part,
upper(left(name, 1)) AS initial,
length(name) AS len,
concat_ws(' / ', name, city) AS label -- NULL city skipped
FROM customers ORDER BY id;
-- ana@example.com | ana | A | 3 | Ana / Yerevan
-- ben@example.com | ben | B | 3 | Ben / London
-- chloe@example.com | chloe | C | 5 | Chloe / London
-- dev@example.com | dev | D | 3 | Dev
SELECT trim(' Mouse ') AS t,
regexp_replace('+1 (555) 010-99', '[^0-9]', '', 'g') AS digits;
-- Mouse | 155501099
SELECT city, string_agg(name, ', ' ORDER BY name) AS people
FROM customers WHERE city IS NOT NULL GROUP BY city ORDER BY city;
-- London | Ben, Chloe
-- Yerevan | AnaRemember
- String positions start at 1
- concat_ws skips NULLs; || turns the whole result NULL
- string_agg (GROUP_CONCAT, LISTAGG) concatenates across rows
- Functions on a column in WHERE usually bypass a plain index
Watch out for
- Using MySQL's LENGTH on UTF-8 text and getting bytes instead of characters.