String Functions

Intermediate

⏱️ 10 mins read

What You'll Learn

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).

Syntax

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)

Example

SELECT
  CONCAT(first_name, ' ', last_name) AS full_name,
  UPPER(email)                       AS email_upper,
  LENGTH(phone)                      AS phone_length,
  SUBSTRING(phone, 1, 3)             AS area_code,
  TRIM(name)                         AS clean_name,
  REPLACE(phone, '-', '')            AS phone_digits,
  LEFT(name, 1)                      AS initial
FROM customers;

-- Extract email domain
SELECT
  email,
  SUBSTRING(email, POSITION('@' IN email) + 1) AS domain
FROM users;

Common Mistakes

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.

Interview Tips

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.

Practice

Extract the domain from all email addresses (e.g., 'gmail.com' from 'user@gmail.com'). Also show the first 3 characters of each customer's name as their code.

Back to Full Roadmap