Text functions

Part of the free LogicWiz SQL & Databases course, Module 3: Functions and expressions. The queries and practice run in the app on real Postgres.

The delivery app greets every customer as "Hi Aarav Sharma!". Meher wants "Hi Aarav!". The database only stores the full name. Can SQL cut it for her?

You'll learn to

• Change text with UPPER, LOWER, TRIM and REPLACE
• Join pieces of text with || and CONCAT, and know how NULL behaves in each
• Cut text with SPLIT_PART, SUBSTRING, LEFT, RIGHT and POSITION

Learn

A function runs once per row

A function takes a value and gives a new value back. UPPER('chai') gives CHAI.

When you put a function on a column, Postgres runs it for every row and shows the new value in the result. The table itself does not change: you are only changing what you see.

• UPPER(x) / LOWER(x): capital or small letters
• LENGTH(x): how many characters
• TRIM(x): removes spaces at the start and end
• REPLACE(x, 'old', 'new'): swaps every old for new

Shouting, whispering, counting

SELECT name,
  UPPER(name) AS shout,
  LOWER(city) AS city_small,
  LENGTH(name) AS letters
FROM customers;

Give each new column a name with AS. Without it, Postgres calls the column upper or length, which nobody wants in a report.

Joining text: || and CONCAT

SELECT name || ' (' || city || ')' AS label,
  CONCAT('Order from ', name) AS cta
FROM restaurants;

|| glues two pieces of text together. CONCAT(a, b, c, …) does the same job as a function. Spaces are text too, so you have to add them yourself.

NULL inside joined text

SELECT name,
  'Rating: ' || rating AS with_pipes,
  CONCAT('Rating: ', rating) AS with_concat
FROM restaurants
WHERE restaurant_id IN (1, 8);

Pizza Corner is new and has no rating (NULL). With || the whole label becomes NULL. CONCAT skips the NULL and keeps the rest.

Anything || NULL is NULL. One missing value wipes out the whole sentence, so a greeting like 'Hi ' || nickname shows blank for everyone without a nickname. Use CONCAT, or wrap the column in COALESCE(nickname, 'friend') (lesson 3.3).

Cutting text into pieces

SELECT name,
  SPLIT_PART(name, ' ', 1) AS first_name,
  SPLIT_PART(name, ' ', 2) AS surname,
  LEFT(name, 3) AS first_three,
  RIGHT(name, 3) AS last_three,
  SUBSTRING(name FROM 2 FOR 4) AS from_2nd_take_4,
  POSITION(' ' IN name) AS space_at
FROM customers
WHERE customer_id <= 4;

SPLIT_PART(text, separator, n) breaks the text at every separator and gives the n-th piece, counting from 1. POSITION(x IN text) tells you where x starts (0 if it is not there). SUBSTRING(text FROM start FOR length) takes a slice.

Cleaning messy input

SELECT id,
  email,
  LOWER(TRIM(email)) AS clean_email,
  SPLIT_PART(LOWER(TRIM(email)), '@', 2) AS domain
FROM signups;

Users type spaces and capital letters by accident. Functions can sit inside each other: the innermost runs first, so TRIM cleans the spaces and LOWER then fixes the case.

In MySQL, || means OR by default, so 'a' || 'b' gives 0. Always use CONCAT there. MySQL has no SPLIT_PART; use SUBSTRING_INDEX(name, ' ', 1). MySQL's LENGTH counts bytes (Hindi letters take 3 each); CHAR_LENGTH counts characters.

Interview favourites: get the first name from a full name, get the domain from an email, find names that start with a vowel (LEFT(name, 1) IN (…)), and mask a phone number so only the last 4 digits show ('XXXXXX' || RIGHT(phone, 4)).

In one line

Text functions change values per row: UPPER, TRIM, REPLACE, SPLIT_PART, LEFT/RIGHT; || with NULL gives NULL, CONCAT skips it.