PostgreSQL String Functions — Week 2 Cheat Sheet

Case conversion 2 functions

case LOWER( string )

Converts every character to lowercase. Most important function in data cleaning — fixes casing mismatches that cause GROUP BY to split one person into two rows.

SELECT LOWER('Ramesh Shrestha');
-- Result:
ramesh shrestha

-- Real use: normalise before matching
WHERE LOWER(driver_name) = LOWER('ramesh shrestha')

input'Ramesh Shrestha' →'ramesh shrestha'

⚠ Our rides table has both 'ramesh shrestha' and 'Ramesh Shrestha'. Without LOWER, a GROUP BY treats them as two different drivers.

case UPPER( string )

Converts every character to uppercase. Less common in data cleaning but useful for status codes, codes, and labels where you want a canonical uppercase form.

SELECT UPPER('completed');
COMPLETED

-- Normalise status codes
SELECT UPPER(status) FROM rides;

input'completed' →'COMPLETED'

💡 INITCAP('ramesh shrestha') → 'Ramesh Shrestha'. Capitalises first letter of every word — useful for display names after cleaning.

···

Whitespace removal 3 functions

trim TRIM( string )

Removes leading and trailing spaces (both sides). The default behaviour — what you want 95% of the time for CSV data where fields often carry padding.

SELECT TRIM('  Kathmandu  ');
Kathmandu

-- Combined with LOWER — the standard cleaning pair
LOWER(TRIM(city_name))

input' Kathmandu ' →'Kathmandu'

trim LTRIM / RTRIM

LTRIM removes spaces from the left side only. RTRIM removes from the right only. Rarely needed vs TRIM but useful when only one side has the problem.

SELECT LTRIM('   hello');
hello

SELECT RTRIM('hello   ');
hello

LTRIM' hello' →'hello'

💡 TRIM can also strip specific characters: TRIM(BOTH ',' FROM ',hello,') → 'hello'

→

Replace, combine & measure 4 functions

replace REPLACE( string, from, to )

Replaces every occurrence of a substring with another. Case-sensitive. Great for fixing known typos, removing characters, or swapping separators.

-- Fix a known typo in city names
REPLACE(city_name, 'Kathamndu', 'Kathmandu')

-- Remove dashes from phone numbers
REPLACE(phone, '-', '')
-- '98-4100-1234'  →  '9841001234'

-- Replace spaces with underscores
REPLACE('New Road', ' ', '_')
New_Road

input'98-4100-1234' →'9841001234'

combine CONCAT( val1, val2, … )

Joins multiple strings into one. Also written with the || operator. CONCAT ignores NULLs; || returns NULL if any part is NULL.

-- Build a full name from parts
CONCAT(first_name, ' ', last_name)

-- With the || operator
first_name || ' ' || last_name

-- Build a readable label
CONCAT(city_name, ' (', country, ')')
Kathmandu (NP)

CONCAT'Ramesh' + ' ' + 'Shrestha' →'Ramesh Shrestha'

measure LENGTH( string )

Returns the number of characters in a string. Useful for validation — detect values that are suspiciously short/long before inserting them.

SELECT LENGTH('Kathmandu');
9

-- Find suspiciously short names (data issue)
SELECT driver_name FROM rides_staging
WHERE LENGTH(TRIM(driver_name)) < 3;

input'Kathmandu' →9

💡 CHAR_LENGTH() is identical to LENGTH() for plain text. Use LENGTH() for simplicity.

nulls COALESCE( val1, val2, … )

Returns the first non-NULL value from its arguments. Absolutely essential in migration — fills in missing values rather than letting NULLs propagate.

-- Replace NULL payment_method with default
COALESCE(payment_method, 'unknown')

-- Use first available name
COALESCE(preferred_name, full_name, 'Unnamed')

-- Convert NULL to empty string safely
COALESCE(notes, '')

NULL inputNULL, 'unknown' →'unknown'

⚠ If any column in your migration SELECT could be NULL, wrap it with COALESCE before inserting. NULLs in FK columns will fail the constraint.

🔍

Pattern matching LIKE · ILIKE · POSITION · STRPOS

match LIKE & ILIKE

LIKE matches a pattern — % means any characters, _ means exactly one character. ILIKE is the same but case-insensitive. Use ILIKE for user-facing searches.

-- LIKE: case-sensitive
SELECT * FROM rides WHERE driver_name LIKE 'Ramesh%';
-- Matches: 'Ramesh Shrestha', 'Ramesh K.' etc.
-- Does NOT match: 'ramesh shrestha'

-- ILIKE: case-insensitive (PostgreSQL only)
SELECT * FROM rides WHERE driver_name ILIKE 'ramesh%';
-- Matches: 'Ramesh', 'RAMESH', 'ramesh' all match

-- Wildcard on both sides = contains
WHERE city_name ILIKE '%mandu%'

LIKE 'R%' 'Ramesh' match TRUE

LIKE 'R%' 'ramesh' match FALSE

💡 Wildcards: % = zero or more any characters. _ = exactly one any character. Example: LIKE 'R_mesh' matches 'Ramesh' but not 'Raamesh'.

position POSITION / STRPOS

Returns the position (index) of a substring inside a string. Returns 0 if not found. Strings in PostgreSQL are 1-indexed. Both functions do the same thing, different syntax.

-- POSITION syntax
POSITION('@' IN 'user@example.com')
5

-- STRPOS syntax (same result)
STRPOS('user@example.com', '@')
5

-- Find rows where city has a space (compound names)
WHERE STRPOS(city_name, ' ') > 0

POSITION('@' IN 'user@example.com') 5

not found 0

check STARTS_WITH / ENDS_WITH

Returns TRUE or FALSE. Cleaner than LIKE for simple prefix/suffix checks. STARTS_WITH is case-sensitive. For case-insensitive prefix check, use ILIKE 'prefix%' instead.

STARTS_WITH('Kathmandu', 'Kath')
true

ENDS_WITH('completed', 'ed')
true

-- Filter: only rows where status starts with 'comp'
WHERE STARTS_WITH(status, 'comp')

STARTS_WITH('Kath…', 'Kath') true

✂

Extracting parts of strings SUBSTRING · LEFT · RIGHT · SPLIT_PART

extract SUBSTRING( str FROM pos FOR len )

Extracts a portion of a string starting at position pos for len characters. Positions are 1-indexed. Can also accept a regex pattern (see Panel 4).

-- From position 1, take 4 characters
SUBSTRING('Kathmandu' FROM 1 FOR 4)
Kath

-- Extract year from a date string
SUBSTRING('2024-03-15' FROM 1 FOR 4)
2024

-- Also written as SUBSTR(string, start, length)
SUBSTR('Kathmandu', 5, 5)
mandu

FROM 1 FOR 4'Kathmandu' →'Kath'

extract LEFT( string, n ) & RIGHT( string, n )

LEFT returns the first n characters. RIGHT returns the last n characters. Simpler than SUBSTRING when you just need the beginning or end.

LEFT('Kathmandu', 4)
Kath

RIGHT('Kathmandu', 4)
andu

-- Get two-letter country code from end
RIGHT('Kathmandu NP', 2)
NP

LEFT 4'Kathmandu' →'Kath'

RIGHT 4'Kathmandu' →'andu'

split SPLIT_PART( string, delimiter, n )

Splits a string on a delimiter and returns the nth part (1-indexed). Extremely useful for CSV-style columns, email addresses, or any delimited data stored as text.

-- Split email into username and domain
SPLIT_PART('ramesh@example.com', '@', 1)
ramesh

SPLIT_PART('ramesh@example.com', '@', 2)
example.com

-- Extract city from "City, Province" format
SPLIT_PART('Kathmandu, Bagmati', ',', 1)
Kathmandu

-- Get last segment of a path
SPLIT_PART('/data/2024/rides.csv', '/', 4)
rides.csv

part 1 of '@''ramesh@example.com' →'ramesh'

📌 Returns an empty string (not NULL) if the requested part doesn't exist. Part numbers start at 1, not 0.

pad LPAD / RPAD

Pads a string to a target length by adding characters on the left or right. Useful for formatting IDs, codes, and fixed-width output columns.

-- Zero-pad a numeric ID to 6 digits
LPAD(CAST(driver_id AS TEXT), 6, '0')
-- 42  →  '000042'

-- Right-pad to align column output
RPAD(city_name, 15, ' ')
-- 'Dharan         '

LPAD(42, 6, '0') →'000042'

Extraction functions side by side same input: 'Ramesh Shrestha'

function	call	result
LEFT	LEFT('Ramesh Shrestha', 6)	'Ramesh'
RIGHT	RIGHT('Ramesh Shrestha', 8)	'Shrestha'
SUBSTRING	SUBSTRING('Ramesh Shrestha' FROM 8 FOR 8)	'Shrestha'
SPLIT_PART	SPLIT_PART('Ramesh Shrestha', ' ', 1)	'Ramesh'
SPLIT_PART	SPLIT_PART('Ramesh Shrestha', ' ', 2)	'Shrestha'
LENGTH	LENGTH('Ramesh Shrestha')	15

regexp_replace — pattern-based cleaning

What is a regular expression (regex)?

A regex is a pattern that describes a set of strings. Instead of writing REPLACE('abc', 'a', '') for one specific character, a regex lets you say "remove any digit" or "remove all characters that aren't letters" — applying to millions of different variations with one expression.

pattern	what it matches	example match
[0-9]	any single digit	'9' in 'NP9841'
[^0-9]	any character that is NOT a digit	'N', 'P' in 'NP9841'
[a-zA-Z]	any letter	'R' in 'Ramesh'
\s	any whitespace (space, tab, newline)	the space in 'New Road'
\s+	one or more whitespace characters	' ' (multiple spaces)
[^a-zA-Z\s]	anything not a letter or space	digits, dashes, brackets
^	start of string	anchors the pattern to the beginning
$	end of string	anchors the pattern to the end

regex REGEXP_REPLACE( string, pattern, replacement, flags )

Replaces parts of a string that match a regex pattern. The flags argument is optional: 'g' replaces all matches (not just the first), 'i' makes it case-insensitive.

-- Syntax
REGEXP_REPLACE(string, pattern, replacement [, flags])

-- Remove ALL digits from a string  (flag 'g' = replace all)
REGEXP_REPLACE('Ramesh123', '[0-9]', '', 'g')
Ramesh

-- Remove ALL non-digit characters  (keep only numbers)
REGEXP_REPLACE('98-4100 1234', '[^0-9]', '', 'g')
9841001234

-- Collapse multiple spaces into one
REGEXP_REPLACE('New   Road', '\s+', ' ', 'g')
New Road

-- Remove all special characters, keep letters + spaces
REGEXP_REPLACE(city_name, '[^a-zA-Z\s]', '', 'g')

-- Case-insensitive flag 'i': remove 'np' or 'NP'
REGEXP_REPLACE('Kathmandu NP', 'np$', '', 'gi')
Kathmandu 

Try it — REGEXP_REPLACE playground

Input string

Pattern (regex)

Replacement

Flags (g / i / gi)

Result

REPLACE vs REGEXP_REPLACE — when to use which

	REPLACE	REGEXP_REPLACE
Use when	You know the exact string to find	You need to match a pattern or character class
Example need	Fix one specific typo everywhere	Remove all digits, collapse spaces, strip symbols
Case sensitive	Always yes	Yes by default; add 'i' flag for insensitive
Replace all	Always replaces all	Only all if you pass 'g' flag
Example	REPLACE(phone,'-','')	REGEXP_REPLACE(phone,'[^0-9]','','g')

→

Putting it all together — data migration cleaning patterns

These are the actual patterns you'll write in the Week 2 migration script. Each one combines multiple string functions to solve a real problem in the rides.csv data.

Normalise driver and rider names before lookup

The flat table has casing inconsistencies. When inserting into the drivers lookup table and when searching by name, always apply LOWER + TRIM together.

-- Insert distinct driver names, cleaned
INSERT INTO drivers (name)
SELECT DISTINCT
    INITCAP(LOWER(TRIM(driver_name)))
FROM rides_staging
WHERE TRIM(driver_name) != ''
  AND driver_name IS NOT NULL;

-- Later: match using the same normalisation
SELECT driver_id FROM drivers
WHERE name = INITCAP(LOWER(TRIM(rs.driver_name)))

Clean city names — strip symbols and collapse spaces

City data from CSVs often has extra punctuation, trailing commas, or double spaces. Chain TRIM + REGEXP_REPLACE to produce a canonical city name.

-- Remove anything that isn't a letter, digit, or space
-- then collapse multiple spaces, then trim edges
TRIM(
  REGEXP_REPLACE(
    REGEXP_REPLACE(city_name, '[^a-zA-Z0-9\s]', '', 'g'),
    '\s+', ' ', 'g'
  )
)

-- Example transformations:
-- '  Kath,mandu!!'  →  'Kathmandu'
-- 'New  Road  '     →  'New Road'
-- 'Dharan, '        →  'Dharan'

Normalise status values

The status column may have 'Completed', 'COMPLETED', 'completed' all meaning the same thing. Standardise to lowercase before inserting.

LOWER(TRIM(status)) AS status

-- Input variations → Output
-- 'Completed '  →  'completed'
-- 'CANCELLED'   →  'cancelled'
-- ' pending'    →  'pending'

-- Add a CHECK constraint so future data stays clean
status TEXT CHECK(status IN ('completed', 'cancelled', 'pending'))

Handle NULLs and empty strings safely

Empty strings ('') are not the same as NULL in PostgreSQL. Use NULLIF to convert empty strings to NULL, then COALESCE to provide a default.

-- Convert empty strings to NULL first
NULLIF(TRIM(payment_method), '')
-- '   '  →  NULL  (instead of '   ')

-- Then provide a default for NULLs
COALESCE(NULLIF(TRIM(payment_method), ''), 'unknown')
-- ''     →  'unknown'
-- NULL   →  'unknown'
-- 'cash' →  'cash'

⚠ TRIM(' ') returns '' (empty string), NOT NULL. Always follow TRIM with NULLIF if you want to treat blank-only values as missing data.

Full migration SELECT — all cleaning combined

This is what the INSERT INTO trips … SELECT will look like when you write it in class. Each column goes through the appropriate cleaning before being inserted.

INSERT INTO trips
    (driver_id, rider_id, pickup_location_id, dropoff_location_id,
     fare_amount, distance_km, status, payment_method, rating)
SELECT
    -- Resolve FK: look up driver_id using cleaned name
    (SELECT d.driver_id FROM drivers d
     WHERE  d.name = INITCAP(LOWER(TRIM(rs.driver_name))))  AS driver_id,

    (SELECT r.rider_id FROM riders r
     WHERE  r.name = INITCAP(LOWER(TRIM(rs.rider_name))))   AS rider_id,

    (SELECT l.location_id FROM locations l
     WHERE  l.city_name = TRIM(rs.pickup_city))              AS pickup_location_id,

    (SELECT l.location_id FROM locations l
     WHERE  l.city_name = TRIM(rs.dropoff_city))             AS dropoff_location_id,

    rs.fare_amount,
    rs.distance_km,
    LOWER(TRIM(rs.status))                                  AS status,
    COALESCE(NULLIF(TRIM(rs.payment_method), ''), 'unknown') AS payment_method,
    rs.rating
FROM rides_staging rs;

≡

All functions at a glance

Case & whitespace

function	what it does	migration use case
LOWER(s)	all lowercase	normalise names before lookup
UPPER(s)	all uppercase	canonical status codes
INITCAP(s)	Title Case every word	clean display name after LOWER
TRIM(s)	remove leading + trailing spaces	every column before comparing or inserting
LTRIM(s)	leading spaces only	rarely needed; use TRIM instead
RTRIM(s)	trailing spaces only	rarely needed; use TRIM instead

Replace & combine

function	what it does	migration use case
REPLACE(s, from, to)	swap exact substring	fix known typos in city names
REGEXP_REPLACE(s, pat, rep, 'g')	pattern-based replace	remove symbols, collapse spaces, strip non-letters
CONCAT(a, b, …)	join strings	build full names from first+last, add labels
a \|\| b	join strings (operator)	same as CONCAT; returns NULL if any part is NULL

Extract & split

function	what it does	migration use case
LENGTH(s)	character count	validate minimum name length before insert
LEFT(s, n)	first n chars	extract prefix codes, country codes
RIGHT(s, n)	last n chars	extract suffix, two-letter codes
SUBSTRING(s FROM p FOR n)	n chars starting at position p	extract year from date string
SPLIT_PART(s, delim, n)	nth segment after splitting on delimiter	parse 'City, Province' into parts
POSITION(sub IN s)	position of substring, 0 if not found	check if '@' present to validate emails
STRPOS(s, sub)	same as POSITION, different syntax	same use cases
LPAD(s, n, fill)	left-pad to length n	zero-pad integer IDs for display
RPAD(s, n, fill)	right-pad to length n	align fixed-width output columns

NULL handling

function	what it does	migration use case
COALESCE(v1, v2, …)	first non-NULL value	default value for missing payment_method, notes
NULLIF(v, compare)	returns NULL if v = compare, else v	convert empty string '' to NULL before COALESCE

Pattern matching

function / operator	what it does	migration use case
col LIKE 'pattern'	pattern match, case-sensitive	filter rows by prefix/suffix
col ILIKE 'pattern'	pattern match, case-insensitive	case-safe search in WHERE clauses
STARTS_WITH(s, prefix)	true if s starts with prefix	check status starts with 'comp'

📌

The golden cleaning combination

INITCAP(LOWER(TRIM(column_name)))

Apply this to every name column (drivers, riders, cities) before inserting into lookup tables and before matching in WHERE clauses. Handles spaces, mixed case, and leading/trailing whitespace in one expression.