Why String Functions Matter
Real-world data is rarely clean — names with inconsistent casing, phone numbers with stray punctuation, concatenated fields that need splitting apart. String functions are the tools for cleaning, transforming, and extracting text directly in SQL, and they're used constantly in both data cleaning pipelines and everyday reporting queries.
Concatenation: Joining Strings Together
-- Standard SQL / PostgreSQL: the || operator
SELECT first_name || ' ' || last_name AS full_name FROM employees;
-- CONCAT() works across PostgreSQL, MySQL, and SQL Server
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- CONCAT_WS (with separator) avoids repeating the separator between every argument
SELECT CONCAT_WS(', ', city, state, zip) AS address FROM locations;NULL behavior differs: in PostgreSQL and standard SQL, || concatenated with a NULL returns NULL for the whole expression — a single missing value silently blanks the entire result. CONCAT() in MySQL and SQL Server, and CONCAT_WS() everywhere, treat NULL as an empty string instead, which is usually the more forgiving and expected behavior. This distinction is a common source of "why is this row blank" bugs.
SUBSTRING: Extracting Part of a String
-- Standard syntax, works on PostgreSQL, MySQL, SQL Server
SELECT SUBSTRING(phone, 1, 3) AS area_code FROM contacts;
-- Positions are 1-indexed: start at character 1, take 3 characters
SELECT SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain FROM users;
-- Extract everything after the @ symbol — a common pattern for parsing emailsCombine with LENGTH()/LEN() to grab from the end of a string when the position isn't fixed:
-- Last 4 digits of a string, regardless of total length
SELECT RIGHT(ssn, 4) AS last_four FROM records; -- MySQL, SQL Server, PostgreSQL all support RIGHT()TRIM: Removing Unwanted Characters
SELECT TRIM(name) FROM users; -- removes leading/trailing whitespace
SELECT TRIM(LEADING '0' FROM zip_code) FROM addresses; -- removes leading zeros only
SELECT TRIM(BOTH '*' FROM promo_code) FROM promotions; -- removes a specific character from both endsTrailing whitespace is a classic silent data-quality bug — 'alice@example.com ' (with a trailing space) fails a naive equality match against 'alice@example.com', and the two look identical when printed. Always suspect a whitespace issue when an equality filter that "should" match returns nothing.
REPLACE: Substituting Text
SELECT REPLACE(phone, '-', '') AS digits_only FROM contacts;
-- '555-123-4567' becomes '5551234567'
-- Chaining REPLACE calls to strip multiple characters
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS digits_only FROM contacts;REPLACE() only handles literal, fixed substrings — for pattern-based replacement (removing any non-digit character, for example, regardless of what it is), you need a regular expression function instead.
Case Conversion and Padding
SELECT UPPER(country_code), LOWER(email) FROM users;
-- Zero-padding a numeric ID to a fixed width — common for generating display codes
SELECT LPAD(order_id::text, 6, '0') AS order_code FROM orders;
-- 42 becomes '000042'Case-insensitive comparisons are a frequent, easy-to-miss bug source: WHERE email = 'Alice@Example.com' won't match a stored 'alice@example.com' on a case-sensitive collation. Normalize both sides with LOWER(), or better, enforce lowercase storage at write time with a CHECK constraint or trigger.
Regular Expressions: Pattern Matching and Extraction
Regex functions handle the cases fixed-string functions can't — validating formats, extracting variable-position substrings, or matching flexible patterns.
-- PostgreSQL: ~ for regex match, regexp_replace for pattern-based substitution
SELECT * FROM users WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
SELECT regexp_replace(phone, '[^0-9]', '', 'g') AS digits_only FROM contacts;
-- MySQL: REGEXP for matching, REGEXP_REPLACE (8.0+) for substitution
SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$';
-- SQL Server: no native REGEXP prior to 2025-era additions; historically required
-- CLR functions or LIKE-based approximations for pattern matchingDatabase compatibility note: regex support and syntax vary significantly more than the basic string functions above — always check your specific database version before relying on a regex-based approach, especially on SQL Server, which lagged the others on native regex support for a long time.
A Realistic Data-Cleaning Example
-- Normalize messy phone numbers into a consistent format
SELECT
raw_phone,
regexp_replace(raw_phone, '[^0-9]', '', 'g') AS digits_only,
CASE
WHEN LENGTH(regexp_replace(raw_phone, '[^0-9]', '', 'g')) = 10
THEN CONCAT(
'(', SUBSTRING(regexp_replace(raw_phone, '[^0-9]', '', 'g'), 1, 3), ') ',
SUBSTRING(regexp_replace(raw_phone, '[^0-9]', '', 'g'), 4, 3), '-',
SUBSTRING(regexp_replace(raw_phone, '[^0-9]', '', 'g'), 7, 4)
)
ELSE NULL -- flag anything that doesn't have exactly 10 digits for manual review
END AS formatted_phone
FROM raw_contacts;This kind of layered string transformation — strip, validate length, reformat — is a realistic pattern for a data-cleaning interview question, and shows fluency across several functions at once rather than just one in isolation.
Common Mistakes
- Assuming || and CONCAT() handle NULL the same way — || returns NULL for the entire expression on a NULL input in PostgreSQL/standard SQL; CONCAT() typically treats NULL as empty. Verify your specific database's behavior.
- Forgetting SUBSTRING is 1-indexed, not 0-indexed — a common off-by-one mistake for anyone coming from most programming languages.
- Not trimming whitespace before comparing or deduplicating strings — silently causes "duplicate" values to be treated as distinct, or exact matches to fail.
- Using REPLACE for pattern-based cleaning instead of a regex function — REPLACE only removes one exact literal substring per call; anything more flexible needs REGEXP_REPLACE.
Common Interview Questions
- Write a query to extract the domain from an email address. The SUBSTRING + POSITION pattern shown above.
- How would you standardize inconsistent casing in a text column? Wrap with UPPER()/LOWER() for comparison, or normalize storage at write time.
- How would you validate that a column matches a specific format (e.g. an email pattern)? A regex match function (~ in PostgreSQL, REGEXP in MySQL), as shown above.
- Why might a WHERE clause fail to match values that look identical when printed? Trailing/leading whitespace or case-sensitivity — always suspect this first when an equality filter unexpectedly returns nothing.
Frequently Asked Questions
Are string functions portable across databases?
The basics (UPPER, LOWER, TRIM, SUBSTRING, REPLACE, LENGTH) are largely standard and work similarly everywhere. Regex support and exact syntax vary the most — always check your specific database's documentation before relying on it.
Is it better to clean strings in SQL or in application code?
For data already in the database, or bulk cleaning operations, SQL is usually faster and avoids round-tripping large volumes of data to the application. For complex, business-logic-heavy validation, application code is often clearer to maintain — a reasonable rule is: simple, mechanical transformations belong in SQL; complex business rules belong in application code.
Practice String Functions
String manipulation shows up constantly in real data cleaning and reporting work. Try it hands-on with our SQL practice questions, or work through a messy real-world dataset in our case studies. For the related date/time functions used alongside string cleaning, see our SQL date and time functions guide.