MODULE 2 · SESSION 09

String Functions:
Text Transformation

Scalar functions clean messy names, slice emails, extract domain names, and join text safely without NULL propagation bugs. All in PostgreSQL 18, on RetailMart.

24

Scenes

1

Practice Arena

4

Practice Drills

5

Interview Questions

Today’s Agenda — String Functions

Four pillars: from messy text typed every possible way to clean, consistent values

Pillar 01

🔤 Case & Spaces

01 Case: UPPER, LOWER, INITCAP

02 Spaces: TRIM, LTRIM, RTRIM

03 Counting: LENGTH vs OCTET_LENGTH

Pillar 02

✂️ Cutting Text

04 1-based slicing: SUBSTRING FROM x FOR y

05 Fast edges: LEFT() & RIGHT()

06 Finding: POSITION() & STRPOS()

Pillar 03

📧 Splitting

07 By a delimiter: SPLIT_PART()

08 Emails: username vs domain

09 Missing parts: empty results

Pillar 04

🔗 Joining & Replace

10 The || trap: one NULL, whole name NULL

11 NULL-safe: CONCAT() & CONCAT_WS()

12 Clean-up: REPLACE()

💡

Why this matters: customers.customers has 50,000 emails, and 8,369 of them are Gmail. Finding that, pulling out a domain, or printing a clean full name are all text jobs. Every report that shows a name, an email or a phone number needs today’s functions.

Where We Left Off

From choosing rows to cleaning what is inside them

📊 SESSIONS 7–8 • COMPLETED

Which Rows, In What Order

-- Session 8: the real top 10, stable pages

SELECT product_id, product_name, price

FROM products.products

ORDER BY price DESC NULLS LAST, product_id

LIMIT 10;

-- Top: HP Deserunt X, ₹1,49,578.93

Session 7 picked the right rows (WHERE, IS NULL, LIKE, IN). Session 8 put them in order and cut pages (ORDER BY, LIMIT, OFFSET, keyset). But the values inside the cells never changed.

✓ WHERE + ORDER BY: the right rows, the right order

🔤 SESSION 09 • THE MISSION

Same Rows, Cleaner Values

-- “Which email providers do our customers use?”

SELECT email,

SPLIT_PART(email, '@', 2) AS domain

FROM customers.customers

ORDER BY customer_id LIMIT 3;

-- [email protected] → bmw.com

A string function works on one row at a time and gives back one value for that row. 50,000 rows in, 50,000 rows out: the table itself is never changed.

🔤 Today: UPPER, TRIM, SUBSTRING, SPLIT_PART, CONCAT_WS, REPLACE

💡

The Core Rule: string functions never change how many rows come back. WHERE decides which rows survive, ORDER BY decides their order, and today’s functions decide what each value looks like.

💌

The Wedding Invitation Disaster 10,000 CARDS, ONE SQL QUERY

LUXURY EVENTS • PRINT RUN EXAMPLE STORY • NAMES & NUMBERS MADE UP 🕘 PRINTER NEEDS THE LIST BY 6 PM

STEP 01 • THE COMPLAINT

events-manager 9:15 AM

“The printer sent back the first proofs. Half the cards say ‘Dear NULL’, some names start with a gap, and one says ‘Dear aArAv’. Reprinting will cost ₹4.8 lakh.”

The names came straight from customers.first_name and last_name, joined with ||.

STEP 02 • THREE BUGS

🔣 2,400 Cards Say “Dear NULL”

NULL in ||

24% of the list had no last name. first_name || ' ' || last_name: one NULL part makes the whole name NULL, and the print tool wrote “NULL”.

␣ Names With Hidden Spaces

No TRIM

‘ Priya ’ was typed with spaces around it. The card printed a gap before the name, and ‘Priya’ and ‘ Priya’ counted as two people.

🔠 rAhUl and AARAV

No INITCAP

Names typed in random capitals went straight onto gold-embossed cards. Nobody wants an invitation addressed to ‘aArAv’.

STEP 03 • THE FIX

ONE QUERY

🐙 Clean, Safe Names

SELECT CONCAT_WS(' ',
         INITCAP(TRIM(first_name)),
         INITCAP(TRIM(last_name)))
       AS invite_name
FROM customers.customers
ORDER BY customer_id
LIMIT 10;
-- Meera Naidu, Lakshmi Singh, Zoya Lal ...

✓ Trimmed, Title Case A NULL part is skipped

STEP 04 • CORE PRINCIPLE

The Moral: Never print raw text. Clean each part first (TRIM, then INITCAP), and join with CONCAT_WS, which skips a NULL part instead of turning the whole name into NULL.

Mapping Text Disasters to PostgreSQL Functions

Every failure in the story corresponds directly to an essential SQL scalar string tool.

🔣 Case & Space Disasters

Problem: 'rAhUl' and ' Sharma '
PostgreSQL Weapon: INITCAP(), UPPER(), LOWER(), and TRIM().
Guarantees consistent capitalization and trims invisible space buffers before reports or joins.

💣 The NULL Name Collapse

Problem: 'Priya' || NULL → NULL
PostgreSQL Weapon: CONCAT() & CONCAT_WS().
Ignores NULLs safely and injects clean whitespace separators without corrupting the joined string.

🔗 Slicing & Delimiter Splitting

Problem: Extracting usernames and domains from email fields.
PostgreSQL Weapon: SPLIT_PART(), SUBSTRING(), and POSITION().
Parses compound strings and isolates segments using 1-based indexing.

Visual Studio: The String Cleanser & Case Transformer

Test per-row text transformation functions live on dirty customer input strings.

TEST OPERATION:

TRANSFORMED OUTPUT PREVIEW:

"Rahul Kumar Sharma"

👁 INITCAP(TRIM(input)) produced the production-grade canonical name: 'Rahul Kumar Sharma'. Leading and trailing spaces were pruned, and every word was given initial title casing.

Definition & Syntax: Case Conversion & Trimming

Make messy text consistent: same case, no extra spaces.

UPPER / LOWER Universal Normalization

Converts entire string to upper or lower case. Critical for case-insensitive search comparisons (e.g. WHERE LOWER(email) = '[email protected]').

INITCAP Title Casing

Capital first letter of every word, the rest lowercase. A word ends at any space or symbol, so 'aarav-mehta' becomes 'Aarav-Mehta'. Good for people's names and cities.

TRIM / BTRIM Space Pruning

Strips leading and trailing spaces. Use LTRIM() for leading only, RTRIM() for trailing only, or BTRIM(str, 'xyz') to strip custom character sets.

SQL DEMOCleaning messy names

-- Messy names, the way real sign-up forms collect them
SELECT
  raw_name,
  UPPER(raw_name) AS upper_name,
  INITCAP(TRIM(raw_name)) AS clean_name,
  LENGTH(raw_name) AS raw_len,
  LENGTH(TRIM(raw_name)) AS clean_len
FROM (VALUES ('   rAhUl sHaRmA  '), ('PRIYA nair'), ('  aarav-mehta')) AS t(raw_name);

Visual Studio: The Text Slicer & 1-Based Indexing

Observe how PostgreSQL extracts slices from text with 1-based indexing.

SLICE TECHNIQUE:

BASE STRING: "RetailMart-Analytics" (Length: 20)

SUBSTRING('RetailMart-Analytics' FROM 1 FOR 10) → 'RetailMart'

👁 Positions start at 1 in SQL: position 1 is 'R', and FOR 10 takes 10 characters. Python and JavaScript start at 0; SQL does not.

Definition & Syntax: Text Slicing & Position Finding

Cut a piece out of text by position (SUBSTRING, LEFT, RIGHT) or by a separator (SPLIT_PART).

SUBSTRING Slice by Offset

SUBSTRING(str FROM start FOR len) extracts a slice of given length starting at 1-based position. Length is optional (defaults to end of string).

SPLIT_PART Delimiter Slicing

SPLIT_PART(string, delimiter, field) splits on a delimiter character and returns the Nth 1-based field. If field doesn't exist, returns empty string ''.

POSITION / STRPOS Locator

POSITION(substr IN str) or STRPOS(str, substr) returns the 1-based index where the substring first occurs. Returns 0 if not found.

SQL DEMOBrand word from RetailMart product names

-- Product names start with the brand: 'Samsung Animi Max'
SELECT
  product_id,
  product_name,
  SPLIT_PART(product_name, ' ', 1) AS brand_word,
  LEFT(product_name, 3) AS first_3,
  POSITION(' ' IN product_name) AS first_space,
  SUBSTRING(product_name FROM 1 FOR POSITION(' ' IN product_name) - 1) AS brand_by_substring
FROM products.products
LIMIT 10;

Visual Studio: The Concatenation Bridge & The NULL Trap

See why the || operator is lethal on nullable columns and how CONCAT rescues data.

TEST CONCATENATION:

EVALUATION RESULT:

'Rahul' || ' ' || NULL → NULL ⚠️ (Lethal Propagation)

👁 CRITICAL TRAP: with ||, one NULL part turns the whole string into NULL!

Definition & Syntax: Safe Concatenation & Replacement

Join text that may have NULLs in it, and swap one piece of text for another.

CONCAT Null-Safe Join

CONCAT(val1, val2, ...) turns every part into text and treats NULL as empty, so one missing part never wipes out the result. Watch the double space it can leave.

CONCAT_WS With Separator

CONCAT_WS(separator, v1, v2, ...) puts the first argument between the parts and skips NULL parts completely: no double spaces, no ', ,'.

REPLACE String Substitution

REPLACE(string, from_substr, to_substr) replaces all occurrences of a target string. Case-sensitive; returns unchanged string if not found.

SQL DEMOThe NULL trap, side by side

-- Rahul has no middle name (NULL). Compare the three ways to join.
SELECT
  first_name,
  middle_name,
  first_name || ' ' || middle_name || ' ' || last_name AS pipe_result,
  CONCAT(first_name, ' ', middle_name, ' ', last_name) AS concat_result,
  CONCAT_WS(' ', first_name, middle_name, last_name) AS concat_ws_result,
  REPLACE(phone, '-', '') AS phone_digits
FROM (VALUES ('Rahul', NULL, 'Sharma', '98765-43210'),
             ('Priya', 'K', 'Nair', '91234-56789')) AS t(first_name, middle_name, last_name, phone);

Pro Tips & Common String Pitfalls

Two mistakes and two habits that save you from silent wrong answers.

⚠ Mistake 1: The Zero-Index Assumption

Wrong: SUBSTRING(code FROM 0 FOR 2)
Why: PostgreSQL is 1-indexed. Position 0 is one step before the first character, so FROM 0 FOR 2 returns only 1 character.
Fixed: Always start at 1 for the first character.

⚠ Mistake 2: The Naive Pipe || Operator

Wrong: first_name || ' ' || middle_name || ' ' || last_name
Why: If middle_name is NULL, the entire output evaluates to NULL.
Fixed: Use CONCAT_WS(' ', first_name, middle_name, last_name).

✔ Pro Tip 1: LENGTH vs OCTET_LENGTH

LENGTH('हिंदी') returns 5 (characters), while OCTET_LENGTH('हिंदी') returns 15 (bytes in UTF-8). Use LENGTH() to count characters. And LENGTH(NULL) is NULL, not 0: WHERE LENGTH(phone) = 10 silently skips rows with no phone.

✔ Pro Tip 2: SPLIT_PART Empty String Guard

When a token is missing, SPLIT_PART returns an empty string '', not NULL. Wrap with NULLIF(SPLIT_PART(...), '') so downstream logic works properly.

Real World: RetailMart Customer Data Audit

Audit the phone numbers of all 50,000 RetailMart customers, and mask them before sharing.

🚀 PRODUCTION PATTERN RetailMart's names are already clean, but its phone numbers are not: 30,182 have 10 digits, 16,484 have 12 (a 91 country code in front) and 3,334 have fewer than 10. LENGTH finds them; RIGHT masks them.

REAL WORLDPhone audit on customers.customers

-- Phones that are not exactly 10 digits, masked before anyone sees them
SELECT
  customer_id,
  INITCAP(first_name) || ' ' || INITCAP(last_name) AS full_name,
  LENGTH(phone) AS digits,
  'XXXXXX' || RIGHT(phone, 4) AS masked_phone,
  SPLIT_PART(email, '@', 2) AS email_domain
FROM customers.customers
WHERE phone IS NOT NULL
  AND LENGTH(phone) <> 10
LIMIT 12;

65%

🧠Practice Time

Try It Live: 4 Quick String Challenges

Run each one, then change it for the "your turn" line. All on real RetailMart tables.

TRY ITReverse name: 'NAIDU M.'

SELECT
  UPPER(last_name) || ' ' ||
  UPPER(LEFT(first_name, 1)) || '.' AS short_name
FROM customers.customers
LIMIT 10;

Your turn: Make it 'M. Naidu': the initial, a dot, a space, then INITCAP(last_name).

TRY ITLong email usernames

SELECT email,
  SPLIT_PART(email, '@', 1) AS username,
  LENGTH(SPLIT_PART(email, '@', 1)) AS len
FROM customers.customers
WHERE LENGTH(SPLIT_PART(email, '@', 1)) > 15
ORDER BY len DESC
LIMIT 10;

Your turn: Find the 10 shortest usernames instead. Which ORDER BY do you need?

TRY ITWord count in product names

SELECT product_name,
  LENGTH(product_name)
  - LENGTH(REPLACE(product_name, ' ', '')) + 1 AS words
FROM products.products
ORDER BY words DESC
LIMIT 10;

Your turn: Show only the names with exactly 3 words.

TRY ITStore initials

SELECT store_name,
  UPPER(LEFT(store_name, 1)) ||
  UPPER(LEFT(SPLIT_PART(store_name, ' ', 2), 1)) AS initials
FROM stores.stores
LIMIT 10;

Your turn: Show 'RETAILMART GWALIOR (Gwalior)': UPPER of the name, then the city in brackets.

Pillars 1–4: String Mastery Key Takeaways

Core rules and interview takeaways for String Functions.

🔧 Function Composition

Stack functions from the inside out: INITCAP(TRIM(col)) first prunes outside spaces, then formats words. Always clean before you format.

🔗 SPLIT_PART Over SUBSTRING

When text is split by a known character (@, -, /), use SPLIT_PART. It is faster to write, easier to read, and prevents off-by-one errors.

💡 NULL Defense

Never use || when joining nullable columns without wrapping in COALESCE or replacing with CONCAT_WS().

Interview Studio: Master String Questions

Five core string transformation questions interviewers ask, with production-grade answers.

Q1 · Scalar vs Aggregate

What is the difference between scalar and aggregate functions?

Answer to give:
Scalar functions take one value and return one value, row by row: UPPER('hello') → 'HELLO', so 100 rows in means 100 rows out. Aggregate functions read many rows and return a single summary value: COUNT(*) on customers.customers → 50,000. Scalar functions work anywhere in SELECT, WHERE and ORDER BY; aggregates cannot go in WHERE, and once a plain column sits next to one you need GROUP BY (Sessions 13 and 14).

Self-Check Studio: 6 String Retention Cards

Click any flashcard to flip and test your memory on PostgreSQL string behavior.

CLICK TO FLIP • SELF-CHECK

What does 'Rahul' || NULL evaluate to in PostgreSQL?

ANSWER: NULL. The || concatenation operator returns NULL if any argument is NULL. Use CONCAT() or CONCAT_WS() to treat NULL safely as an empty string.

CLICK TO FLIP • SELF-CHECK

Is PostgreSQL SUBSTRING 0-indexed or 1-indexed?

ANSWER: 1-indexed. The first character sits at position 1. SUBSTRING('PostgreSQL' FROM 1 FOR 4) returns 'Post'. FROM 0 is not an error: it starts one step before the first letter, so SUBSTRING('PostgreSQL' FROM 0 FOR 4) returns only 'Pos'.

CLICK TO FLIP • SELF-CHECK

What does SPLIT_PART('a/b/c', '/', 2) return?

ANSWER: 'b'. It splits by the '/' delimiter and returns the 2nd 1-based element cleanly.

CLICK TO FLIP • SELF-CHECK

What does POSITION('@' IN 'user.com') return when '@' is missing?

ANSWER: 0. In PostgreSQL, POSITION returns 0 if the target substring does not exist. No error and no NULL for a missing match. It returns NULL only when the text itself is NULL.

CLICK TO FLIP • SELF-CHECK

What does TRIM(' hello world ') do to interior spaces?

ANSWER: Nothing. Standard TRIM removes only leading and trailing whitespace. The interior space between 'hello' and 'world' remains intact.

CLICK TO FLIP • SELF-CHECK

Why is INITCAP('IKEA') considered dangerous for catalog branding?

ANSWER: INITCAP converts every word to Title Case: it turns 'IKEA' into 'Ikea' and 'LG' into 'Lg'. Acronyms and brand names get ruined; use TRIM without INITCAP for brand catalogs.

Key Takeaways: The 6 String Laws

Universal principles for robust text transformation and production data sanitization.

01

CONCAT Over || For Safety

|| propagates NULL across the entire expression. Always prefer CONCAT() or CONCAT_WS() when joining nullable columns to prevent accidental NULL outputs.

02

PostgreSQL Is 1-Indexed

String positions in SUBSTRING, POSITION, and STRPOS start at 1, never 0. Thinking in 0-based Python or JavaScript indexes yields silent off-by-one errors.

03

TRIM For Edges, REPLACE For Global

TRIM cleans leading and trailing padding. When unwanted characters live inside the text (like commas in currency numbers), use REPLACE() instead.

04

SPLIT_PART For Delimiters

When extracting parts separated by a fixed character (@, /, comma), use SPLIT_PART(). It is shorter, easier to read, and avoids tedious SUBSTRING + POSITION math.

05

INITCAP For People, Not Brands

INITCAP capitalizes the first letter and forces the rest to lowercase. Great for customer names ('rahul' → 'Rahul'), but it destroys brand acronyms ('LG' → 'Lg').

06

Scalar Output Never Mutates Stored Data

Scalar functions transform output per row in query results. They never alter stored table records on disk. Stored records change only via explicit UPDATE commands.

Quick Reference: String Cheatsheet

Complete PostgreSQL string function index with interactive ▶ Run links.

CASE, MEASUREMENT & TRIMMING

UPPER • LOWER (Case folding)▶ Run

SELECT UPPER('retailmart'), LOWER('RETAILMART');

INITCAP (Title case)▶ Run

SELECT INITCAP('rahul kumar sharma');

TRIM (Whitespace strip)▶ Run

SELECT TRIM('   clean me   ');

LENGTH (Character count)▶ Run

SELECT LENGTH('RetailMart');

EXTRACTION, SLICING & SAFE JOIN

SUBSTRING (1-based slice)▶ Run

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4);

SPLIT_PART (Tokenize on delimiter)▶ Run

SELECT SPLIT_PART('[email protected]', '@', 2);

POSITION (Find substring offset)▶ Run

SELECT POSITION('@' IN '[email protected]');

CONCAT_WS (Null-safe separator join)▶ Run

SELECT CONCAT_WS(', ', 'A', NULL, 'B');

REPLACE (Global substring swap)▶ Run

SELECT REPLACE('v1.0', 'v1', 'v2');

Take-Home Challenge: 4 String Queries on RetailMart

Only today's string functions, no joins and no GROUP BY. Write each one in the playground, then check your row count against the one given.

01

Proper-case customer names

From customers.customers, format names as 'Sharma, Rahul' (last_name, first_name) using INITCAP and ||. Show 20 customers.

02

Mask phone numbers

Show customer_id and masked phone numbers as 'XXXXXX' + last 4 digits using RIGHT(). Filter out NULL phone numbers anyway: RetailMart has none, real tables do (50,000 rows).

03

Customer email domain

Extract each customer's email domain using SPLIT_PART(email, '@', 2). Filter specifically for gmail.com users using LIKE (8,369 customers).

04

Long product names

Find products in products.products with names longer than 30 characters (11 products): display product_name, its LENGTH, and its first word using SPLIT_PART.

✓

Self-Correction Tip

Every task uses a function from today's Quick Reference. Open the playground, write the query, and verify the output against sample rows.

Your SQL Journey: Module 2 Unlocked

Track your progress across the 59-session SQL course.

MODULE 1 S01–S08: Foundations COMPLETED ✓

MODULE 2 S09: String Functions YOU ARE HERE ●

MODULE 2 S10: Date & Time Basics UP NEXT

MODULE 2 S11–S12: CASE, CAST & NULLs LOCKED

MODULE 2 S13–S23: Aggregates & Joins LOCKED

MODULES 3–6 S24–S59: Advanced SQL LOCKED

Next Session Preview: Date & Time Basics

Coming next in Module 2: Scalar Functions & Temporal Logic.

Date & Time Basics: Temporal Types, Intervals & Snapping

MODULE 2 · SESSION 10

Master temporal types (DATE, TIMESTAMP, TIMESTAMPTZ), clocks with NOW() and CURRENT_DATE, calendar arithmetic with INTERVAL and AGE(), date part extraction with EXTRACT(), period snapping with DATE_TRUNC(), and formatting with TO_CHAR().

Date & Time Basics → Review From Beginning ↺

String Functions:Text Transformation

Today’s Agenda — String Functions

Where We Left Off

Which Rows, In What Order

Same Rows, Cleaner Values

Mapping Text Disasters to PostgreSQL Functions

Visual Studio: The String Cleanser & Case Transformer

Definition & Syntax: Case Conversion & Trimming

Visual Studio: The Text Slicer & 1-Based Indexing

Definition & Syntax: Text Slicing & Position Finding

Visual Studio: The Concatenation Bridge & The NULL Trap

Definition & Syntax: Safe Concatenation & Replacement

Pro Tips & Common String Pitfalls

Real World: RetailMart Customer Data Audit

Try It Live: 4 Quick String Challenges

Pillars 1–4: String Mastery Key Takeaways

Interview Studio: Master String Questions

Self-Check Studio: 6 String Retention Cards

Key Takeaways: The 6 String Laws

Quick Reference: String Cheatsheet

Take-Home Challenge: 4 String Queries on RetailMart

Your SQL Journey: Module 2 Unlocked

Next Session Preview: Date & Time Basics

String Functions:
Text Transformation