Q25 of 26 · SQL
Which SQL string functions are most useful in QA work, and give an example of each.
SQLMidsqlstring-functionstrimlikedata-validation
Short answer
Short answer: TRIM, UPPER/LOWER, LIKE, CONCAT, SUBSTRING, LENGTH, and REPLACE — used to normalise data before comparison, validate format, and find malformed records.
Detail
TRIM / LTRIM / RTRIM — removes whitespace. Whitespace bugs are invisible in the UI but break comparisons:
SELECT COUNT(*) FROM users WHERE email != TRIM(email);
-- Non-zero count means the app is storing emails with leading/trailing spaces
UPPER / LOWER — case-insensitive comparison:
SELECT * FROM products WHERE LOWER(name) LIKE '%widget%';
LENGTH — validate field length constraints:
SELECT id, phone FROM users WHERE LENGTH(phone) != 10;
-- Find phone numbers that don't match the expected format
LIKE / ILIKE (PostgreSQL) — pattern matching:
SELECT * FROM users WHERE email NOT LIKE '%@%.%';
-- Emails missing @ or domain dot — format validation
SUBSTRING / SUBSTR — extract part of a string:
SELECT DISTINCT SUBSTRING(phone, 1, 3) AS area_code FROM users;
CONCAT / || — build composite keys or labels:
SELECT first_name || ' ' || last_name AS full_name FROM customers;
REPLACE — clean data before comparison:
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS normalised_phone FROM users;
// EXAMPLE
-- Find users whose email has leading/trailing spaces or uppercase (normalisation bug)
SELECT id, email
FROM users
WHERE email != LOWER(TRIM(email));// WHAT INTERVIEWERS LOOK FOR
At least three functions with QA use cases. The whitespace/case normalisation pattern is the most practically useful to mention.