UPPER, LOWER, SUBSTRING, REPLACE
Normalise case, slice substrings by position and length, and replace patterns inside text.
Case Conversion
Two basic functions:
SELECT UPPER('hello'); -- HELLO
SELECT LOWER('Hello'); -- hello
SELECT INITCAP('hello world'); -- 'Hello World' (PostgreSQL extension)Why Normalise Case
Email comparison should be case-insensitive but case-preserving in storage. Normalise at compare time:
SELECT * FROM users WHERE LOWER(email) = LOWER('USER@Example.COM');
-- Or use a functional index:
CREATE INDEX users_email_lower_idx ON users (LOWER(email));All lessons in this course
- Concatenation: || and CONCAT
- UPPER, LOWER, SUBSTRING, REPLACE
- TRIM, LPAD, RPAD
- Full-Text Search Introduction (tsvector, to_tsquery)