SQL Mastery
Intermediate 14 min read SQL Mastery

String and Date Functions

Clean text and reckon time the way the engine expects

Text mangling and date arithmetic are where most real-world cleaning happens — trimming, parsing, regexps, date parts and timezone math done safely.

stringdateEXTRACTregextimezoneformatting
Prerequisites: SQL Query Fundamentals
Before we start

What you will learn

  • Use common string functions (trim, lower, split, regexp)
  • Format and parse dates and extract parts
  • Handle timezone and offset traps
Introduction

What is this?

String functions carve, normalize and extract from text; date functions build, parse, compare and part-out temporal values. Together they are the cleaning toolkit for the messy source data that never arrives in the shape the analytics layer wants.

Motivation

Why do you need it?

  • Source systems emit mixed case, trailing spaces, typos and multiple date formats.
  • Timezones turn 'what day did the sale happen' into a live question; date functions make it answerable.
  • Trivial parsing mistakes (treating '10.5' as a currency) silently skew aggregates for years.
Think about it

Mental model

Imagine

A deli counter slicing standing orders

Think of

String/date functions

The butcher slices, ties and labels par-cooked orders by date; you order by the label, not by guessing. SQL date/string functions slice raw values into comparable, sortable, groupable pieces.

Mechanics

How it works

  • upper/lower, trim, replace, split/array, substring, length normalize text.
  • regexp_matches/extract match patterns; many engines use RE2 or POSIX ERE.
  • CAST('2026-01-01' AS DATE), DATE('2026-01-01'), TO_DATE/TO_TIMESTAMP parse gracefully or loudly.
  • EXTRACT(part FROM ts) (year, month, dow, hour) parts-out temporal fields for GROUP BY.
  • Timezone conversion (AT TIME ZONE) renders an instant in a target zone; storing naive timestamps is the trap.

From raw text to clean time-partitioned values.

Raw strings
Normalize
Parse
Part-out
Group / compare
Hands-on

Examples

Normalize a kebab-case date column

Normalize a kebab-case date column
SELECT
  LOWER(TRIM(region)) AS region,
  DATE(created_at) AS day
FROM raw_data
WHERE regexp_like(email, '^[^@]+@[^@]+$');

Case and whitespace normalized at the boundary; the date is truncated to day; junk emails excluded by pattern.

Real world

In production

  • A land zone normalizes all text columns (TRIM + LOWER + consolidate NULL) before the modeling layer.
  • A partition generator EXTRACTs year-month from event ts for S3 folder naming.
  • A DST-safe scheduling query advances dates with INTERVAL arithmetic instead of adding 3600*24 with drift.
Guidelines

Best practices

  • Normalize text into a stable case/enum at the raw boundary, not in every downstream report.
  • Store timestamps with an explicit timezone (TIMESTAMPTZ) rather than naive local time.
  • Parse with explicit casts so bad data fails loudly instead of rolling over to next month.
  • Test regexps offline on a sample; catastrophic regexp backtracking is a DoS vector.
Watch out

Common mistakes

  • Ambiguous '01/02/2026' being read as Jan 2 by one engine and Feb 1 by another.
  • Comparing strings as dates ('2026-01-31' < '2026-02-01' fails lexically... actually OK for ISO, fails for '1/31/2026').
  • Ignoring timezone so a daily rollup technically covers two different local days for some users.
  • Locale-dependent formatting (week starts, comma decimals) leaking into CSV exports.
Root cause analysis

Common issues

Error
Dates parsed inconsistently across runs
Root cause

Engine-dependent default date order for ambiguous formats

Fix

Parse with an explicit TO_DATE/TO_TIMESTAMP format mask everywhere.

Error
Daily rollup double-counts a UK sale
Root cause

Event stored naive UTC then bucketed in local (BST) time

Fix

Store TIMESTAMPTZ and bucket with AT TIME ZONE 'Europe/London' explicitly.

When things break

Debugging tips

  • Run problematic strings through a pure expression (SELECT substr(x, 1, 5), length(x)) to inspect them.
  • Check what the engine actually stored: SELECT typeof(x) or inspect the column's formatting.
  • When a date 'mysteriously shifts', print the raw value and the AT TIME ZONE transform side by side.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which is the safe pattern for deriving a day bucket from a timestamp?

2

Mixed date formats are safely handled by letting each engine guess the order.

Go deeper

Official documentation

PostgreSQL string functions
docs
PostgreSQL date/time functions
docs