0Pricing
SQL Academy · Lesson

UPPER, LOWER, SUBSTRING, REPLACE

Normalise case, slice substrings by position and length, and replace patterns inside text.

UPPER, LOWER, SUBSTRING, REPLACE is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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));

Citext: A Case-Insensitive Text Type

For a column that should always compare case-insensitively, use the citext extension:

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE users (email CITEXT UNIQUE);

SUBSTRING

Extract a substring by start and length:

SELECT SUBSTRING('PostgreSQL', 1, 4);    -- 'Post'
SELECT SUBSTRING('PostgreSQL' FROM 5);    -- 'greSQL'

-- 1-based indexing, not 0-based

SUBSTRING with Regex

PostgreSQL also accepts a regex pattern:

SELECT SUBSTRING('user@example.com' FROM '@(.+)$');
-- 'example.com'

LEFT and RIGHT

Shortcuts for "first N" and "last N":

SELECT LEFT('PostgreSQL', 4);     -- 'Post'
SELECT RIGHT('PostgreSQL', 3);    -- 'SQL'

REPLACE

Replace every occurrence of a substring:

SELECT REPLACE('hello world', 'world', 'SQL');   -- 'hello SQL'
SELECT REPLACE(phone, '-', '');                  -- strip dashes

REGEXP_REPLACE

Replace by pattern:

SELECT REGEXP_REPLACE('Order #1234', '[^0-9]', '', 'g');  -- '1234'
SELECT REGEXP_REPLACE(text, '\s+', ' ', 'g');             -- collapse whitespace

LENGTH and CHAR_LENGTH

Length in characters, not bytes (Unicode-aware):

SELECT LENGTH('héllo');           -- 5
SELECT CHAR_LENGTH('héllo');      -- 5
SELECT OCTET_LENGTH('héllo');     -- 6  (UTF-8 byte length)

POSITION and STRPOS

Find the index of a substring:

SELECT POSITION('SQL' IN 'PostgreSQL');    -- 8
SELECT STRPOS('PostgreSQL', 'SQL');        -- 8

REVERSE

Reverse a string:

SELECT REVERSE('hello');     -- 'olleh'

Recap

Case + substring + replace covers most text munging.

  • LOWER/UPPER for normalisation
  • SUBSTRING for slicing
  • REPLACE for fixed swaps, REGEXP_REPLACE for patterns

Quick Check

What does SUBSTRING('database', 5, 4) return?

Frequently asked questions

Is the “UPPER, LOWER, SUBSTRING, REPLACE” lesson free?

Yes — the full text of “UPPER, LOWER, SUBSTRING, REPLACE” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “UPPER, LOWER, SUBSTRING, REPLACE”?

Normalise case, slice substrings by position and length, and replace patterns inside text. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “UPPER, LOWER, SUBSTRING, REPLACE” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Concatenation: || and CONCAT
  2. UPPER, LOWER, SUBSTRING, REPLACE
  3. TRIM, LPAD, RPAD
  4. Full-Text Search Introduction (tsvector, to_tsquery)
← Back to SQL Academy