string functions

Text data is always messy. Extra spaces, random caps, stuff you need to cut or join. These are the functions you'll keep reaching for. Postgres names, mysql differences noted where they matter.

joining text

SELECT first_name || ' ' || last_name FROM customers;       -- postgres / standard
SELECT CONCAT(first_name, ' ', last_name) FROM customers;   -- works in both
SELECT CONCAT_WS(', ', last_name, first_name) FROM customers; -- "with separator"

Difference that matters, || gives NULL if any piece is NULL. CONCAT just treats NULL as empty. CONCAT_WS skips NULLs and doesn't leave double separators.

changing case

SELECT UPPER(first_name), LOWER(email), INITCAP('hello world') FROM customers;
-- INITCAP -> 'Hello World' (postgres only)

Handy for case-insensitive matching, WHERE LOWER(email) = LOWER('Aman@Mail.com').

length

SELECT first_name, LENGTH(first_name) FROM customers;

In mysql LENGTH counts bytes and CHAR_LENGTH counts characters, they differ for emojis/hindi text. In postgres LENGTH counts characters, fine.

cutting pieces out

SELECT LEFT(first_name, 3)  FROM customers;   -- first 3 chars
SELECT RIGHT(phone, 4)      FROM customers;   -- last 4 digits
SELECT SUBSTRING(email FROM 1 FOR 4) FROM customers;  -- postgres style
SELECT SUBSTRING(email, 1, 4) FROM customers;         -- also works

Positions start at 1 in sql, not 0.

finding stuff inside text

SELECT email, POSITION('@' IN email) FROM customers;  -- where's the @
SELECT STRPOS(email, '@') FROM customers;             -- postgres, same thing
-- mysql: LOCATE('@', email) or INSTR(email, '@')

Combine with SUBSTRING to grab the domain:

SELECT email, SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM customers;

Or in postgres just SPLIT_PART(email, '@', 2), way cleaner.

cleaning

SELECT TRIM('   hello   ');          -- 'hello'
SELECT LTRIM('   hello');            -- 'hello'
SELECT RTRIM('hello   ');            -- 'hello'
SELECT TRIM(BOTH '-' FROM '--hi--'); -- 'hi'
SELECT REPLACE(phone, '98765', 'XXXXX') FROM customers;

Always TRIM user input data before comparing, 'Delhi ' with a trailing space is not 'Delhi'.

padding

SELECT LPAD(order_id::text, 6, '0') FROM orders;  -- 000101
SELECT RPAD('ab', 5, '*');                         -- 'ab***'

others worth knowing

SELECT REVERSE('sql');               -- 'lqs'
SELECT REPEAT('ha', 3);              -- 'hahaha'
SELECT ASCII('A'), CHR(65);          -- 65, 'A'
SELECT MD5('hello');                 -- a hash
SELECT STRING_AGG(first_name, ', ') FROM customers;  -- all names in one string
-- mysql: GROUP_CONCAT(first_name SEPARATOR ', ')

STRING_AGG is an aggregate, so it works with GROUP BY, super useful:

SELECT city, STRING_AGG(first_name, ', ' ORDER BY first_name) AS people
FROM customers
GROUP BY city;

regex (postgres)

When LIKE isn't enough.

SELECT * FROM customers WHERE email ~ '^[a-z]+@mail\.com$';  -- matches regex
SELECT * FROM customers WHERE first_name ~* '^a';             -- case insensitive
SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '', 'g');             -- 'abc'
-- mysql uses REGEXP / RLIKE

stuff that trips people up

• || with NULL making the whole thing NULL.
• positions starting at 1, not 0.
• trailing spaces breaking equality checks.
• function names differing across databases, always check which one you're on.

practice

Check queries.sql.

-- string functions (postgres)
-- run sample-data.sql first

-- examples ---------------------------------------------------------

SELECT first_name || ' ' || last_name AS full_name FROM customers;

SELECT UPPER(first_name), LOWER(email), LENGTH(first_name) FROM customers;

SELECT email, SPLIT_PART(email, '@', 2) AS domain FROM customers;

SELECT LPAD(order_id::text, 6, '0') FROM orders;

SELECT city, STRING_AGG(first_name, ', ' ORDER BY first_name) AS people
FROM customers
GROUP BY city;


-- practice ---------------------------------------------------------

-- 1. show customers' initials, like "AS" for Aman Sharma
-- 2. show the username part of each email (before the @)
-- 3. show phone numbers masked like XXXXXX0001 (only last 4 visible), 'no phone' if NULL
-- 4. products whose name is longer than 10 characters
-- 5. for each category, a comma separated list of its product names
-- 6. turn 'Wireless Mouse' into 'wireless-mouse' (a url slug) for every product


-- answers ----------------------------------------------------------

-- 1
SELECT LEFT(first_name, 1) || LEFT(last_name, 1) AS initials FROM customers;

-- 2
SELECT SPLIT_PART(email, '@', 1) AS username FROM customers;
-- or SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1)

-- 3
SELECT first_name, COALESCE('XXXXXX' || RIGHT(phone, 4), 'no phone') AS masked FROM customers;

-- 4
SELECT product_name FROM products WHERE LENGTH(product_name) > 10;

-- 5
SELECT category, STRING_AGG(product_name, ', ') FROM products GROUP BY category;

-- 6
SELECT LOWER(REPLACE(product_name, ' ', '-')) AS slug FROM products;