String & Date Functions

13 min read

String Functions

SQL provides a rich set of built-in functions for manipulating text. The exact function names vary slightly between databases, but the concepts are universal.

UPPER(str) / LOWER(str) — change case
LENGTH(str) — character count
TRIM(str) — remove leading and trailing spaces
SUBSTRING(str, start, len) — extract a portion of a string
REPLACE(str, from, to) — replace all occurrences of a substring
CONCAT(a, b, ...) or a || b — join strings together
LIKE / ILIKE — pattern matching (covered in the Foundations module)

sql

-- Normalise names to title-case style (upper first letter)
-- PostgreSQL example using INITCAP
SELECT INITCAP(name) AS formatted_name FROM students;

-- Extract the domain from email addresses
SELECT
  name,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM students;

-- Mask email for display
SELECT
  name,
  CONCAT(SUBSTRING(email, 1, 3), '***') AS masked_email
FROM students;

Date Functions

Working with dates is one of the most common real-world SQL tasks. Key functions include extracting parts of a date, computing differences, and formatting output.

sql

-- Current date and timestamp
SELECT CURRENT_DATE, CURRENT_TIMESTAMP;

-- Extract year/month/day from enrolled_at
SELECT
  enrolled_at,
  EXTRACT(YEAR  FROM enrolled_at) AS yr,
  EXTRACT(MONTH FROM enrolled_at) AS mo,
  EXTRACT(DAY   FROM enrolled_at) AS dy
FROM enrolments;

-- Enrolments from the last 30 days (PostgreSQL)
SELECT * FROM enrolments
WHERE enrolled_at >= CURRENT_DATE - INTERVAL '30 days';

-- Days since each student enrolled
SELECT
  student_id,
  enrolled_at,
  CURRENT_DATE - enrolled_at AS days_enrolled
FROM enrolments;

Date and string function names differ significantly between databases. SUBSTRING vs SUBSTR, DATEDIFF vs interval arithmetic — always check your engine's documentation. Code that works in PostgreSQL may not work in MySQL without adjustments.

Ready to test yourself?

Practice SQL— quiz & coding exercises