Loading…
Loading…
SQL tutorial · Intermediate
⏱️ 10 mins read
SQL has built-in string functions for data cleaning and transformation. Common ones: CONCAT (join strings), UPPER/LOWER (change case), LENGTH (character count), SUBSTRING/SUBSTR (extract part of string), TRIM (remove whitespace), REPLACE (substitute text), LEFT/RIGHT (extract from ends).
CONCAT(s1, s2)
UPPER(s) / LOWER(s)
LENGTH(s)
SUBSTRING(s, start, length)
TRIM(s) / LTRIM(s) / RTRIM(s)
REPLACE(s, old, new)
LEFT(s, n) / RIGHT(s, n)-- Shape names from the employees table
SELECT
name,
UPPER(name) AS name_upper,
LENGTH(name) AS name_length,
LEFT(name, 1) AS initial,
REPLACE(name, 'a', '@') AS leet_name
FROM employees;
-- Split 'karl@example.com' into local part and domain
SELECT
name,
email,
SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS local_part,
SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM customers;| name |
|---|
| Ada |
| Grace |
| Alan |
| Edsger |
| Barbara |
| name | city | country | |
|---|---|---|---|
| Karl | karl@example.com | Berlin | Germany |
| Ines | ines@example.com | Berlin | Germany |
| Omar | omar@example.com | Berlin | Austria |
| Sofia | sofia@example.com | London | UK |
| Maike | maike@example.com | NULL | NULL |
Topic 3 showed YEAR(hire_date) killing an index on dates. Strings have the same disease: WHERE LOWER(email) = 'karl@example.com' calls LOWER on every row and ignores any index on email. Same fix, same shape — put the transformation on the constant, or index the expression itself.
-- Not sargable: function per row, index on email unusable
SELECT * FROM customers WHERE LOWER(email) = 'karl@example.com';
-- Sargable: compare against a normalized constant instead
SELECT * FROM customers WHERE email = 'karl@example.com';
-- If case-insensitivity is genuinely required: functional index (PostgreSQL)
CREATE INDEX idx_cust_email_lower ON customers (LOWER(email));Concatenate anything with NULL and standard SQL returns NULL — one missing piece blanks the whole string. Maike, our customer with no city, proves it: CONCAT(name, ' — ', city) quietly deletes her from every formatted report. MySQL's CONCAT behaves the same; SQL Server's CONCAT is the outlier, treating NULL as empty.
SELECT
name,
CONCAT(name, ' — ', city) AS label,
CONCAT(name, ' — ', COALESCE(city, '?')) AS label_safe
FROM customers;
-- Karl → 'Karl — Berlin' | 'Karl — Berlin'
-- Maike → NULL ← she vanished | 'Maike — ?'Splitting an email needs no regex: find the '@' with POSITION, slice with SUBSTRING. But POSITION returns 0 when the needle is missing — and SUBSTRING(email, 0) is not an error, it is garbage: everything from a fictional position before the string. Malformed input (an email with no '@') quietly produces broken output.
SELECT
email,
POSITION('@' IN email) AS at_pos,
SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM customers;
-- All five rows work... but feed in 'not-an-email' and at_pos = 0,
-- so SUBSTRING(..., 1) returns the WHOLE string as the 'domain'
-- Defensive version:
SELECT email,
CASE WHEN POSITION('@' IN email) > 0
THEN SUBSTRING(email, POSITION('@' IN email) + 1)
END AS domain
FROM customers;Function names differ by database. SUBSTRING vs SUBSTR, CHARINDEX (SQL Server) vs POSITION (MySQL/PostgreSQL) vs INSTR (Oracle). Always check your DB docs. Also, string functions can prevent index usage — avoid them in WHERE clauses on indexed columns.
Data cleaning questions are common in analytics interviews. Practice extracting domain from email, parsing date from string, cleaning whitespace, and building full names from parts.
1. Why is WHERE LOWER(email) = 'karl@example.com' a performance problem?
2. CONCAT(name, ' — ', city) on Maike (city is NULL) returns…?
3. POSITION('@' IN 'not-an-email') returns…?
4. LENGTH vs CHAR_LENGTH — when do they disagree?
5. What is the difference between '' (empty string) and NULL?
6. Why do function names like SUBSTRING vs SUBSTR matter in interviews?
Return every employee's name converted to uppercase.
⚡ Solve it in the SQL playground →LENGTH counts bytes, CHAR_LENGTH counts characters. With UTF-8, 'ä' is one character but two bytes, so names like 'Jürgen' make the two functions disagree. For human-facing strings, CHAR_LENGTH (or CHARACTER_LENGTH) is almost always what you meant.
TRIM removes spaces (and optionally other characters) from both ends, so ' abc ' → 'abc'. But '' (empty string) is not NULL: LENGTH('') is 0, while LENGTH(NULL) is NULL. Cleaning pipelines must handle both separately — trimming never turns an empty string into NULL.