SQL String Functions Explained — UPPER, TRIM, SUBSTRING and More

-- ============================================================
-- Setup: create a small customers table with intentionally
-- messy data so we can demonstrate each function clearly.
-- ============================================================

CREATE TABLE customers (
    customer_id   INT,
    full_name     VARCHAR(100),
    email         VARCHAR(150),
    city          VARCHAR(50)
);

INSERT INTO customers VALUES
    (1, '  alice johnson  ', 'ALICE@EXAMPLE.COM', 'new york'),
    (2, 'BOB SMITH',         'bob@example.com',   'Los Angeles'),
    (3, 'carol white  ',     'Carol@Example.com', 'CHICAGO');

-- -------------------------------------------------------
-- Quick preview: what does the raw data look like?
-- -------------------------------------------------------
SELECT customer_id, full_name, email, city
FROM   customers;

-- ============================================================
-- UPPER: standardise city names to uppercase for safe grouping
-- ============================================================
SELECT
    UPPER(city)          AS city_uppercase,   -- 'new york' → 'NEW YORK'
    COUNT(*)             AS customer_count
FROM   customers
GROUP BY UPPER(city);                         -- groups correctly despite mixed case

-- ============================================================
-- LOWER: normalise email addresses (emails are case-insensitive
-- but databases treat them as case-sensitive strings)
-- ============================================================
SELECT
    customer_id,
    LOWER(email)  AS normalised_email  -- 'ALICE@EXAMPLE.COM' → 'alice@example.com'
FROM   customers;

-- ============================================================
-- LENGTH: find any city names that seem suspiciously short
-- (useful for catching typos or blank entries)
-- ============================================================
SELECT
    customer_id,
    city,
    LENGTH(city)  AS city_length   -- counts every character including spaces
FROM   customers
WHERE  LENGTH(city) < 5;           -- flag anything under 5 characters as suspect

-- ============================================================
-- PRACTICAL COMBO: case-insensitive search
-- Find all customers in New York regardless of how it was typed
-- ============================================================
SELECT *
FROM   customers
WHERE  LOWER(city) = 'new york';   -- normalise BOTH sides to lowercase

-- ============================================================
-- Basic TRIM: remove spaces from both sides of full_name
-- ============================================================
SELECT
    customer_id,
    full_name,                      -- original, messy version
    TRIM(full_name) AS clean_name,  -- spaces removed from both sides
    LENGTH(full_name)         AS original_length,
    LENGTH(TRIM(full_name))   AS trimmed_length  -- shows how many spaces were hiding
FROM   customers;

-- ============================================================
-- LTRIM vs RTRIM: when you only want to cut one side
-- Useful if you're processing fixed-width data exports where
-- trailing spaces are padding but leading spaces mean something.
-- ============================================================
SELECT
    '  hello world  '           AS original,
    LTRIM('  hello world  ')    AS left_trimmed,   -- 'hello world  '
    RTRIM('  hello world  ')    AS right_trimmed,  -- '  hello world'
    TRIM('  hello world  ')     AS fully_trimmed;  -- 'hello world'

-- ============================================================
-- TRIM a specific character (not just spaces)
-- Imagine prices were imported with a leading dollar sign
-- ============================================================
SELECT
    TRIM(LEADING '$' FROM '$49.99')   AS price_no_prefix,   -- '49.99'
    TRIM(TRAILING '!' FROM 'Sale!')   AS message_no_bang,   -- 'Sale'
    TRIM(BOTH   '*' FROM '***HOT***') AS product_clean;      -- 'HOT'

-- ============================================================
-- PRACTICAL USE: clean names before inserting into a report
-- Combine TRIM + UPPER to fully normalise a string
-- ============================================================
SELECT
    customer_id,
    UPPER(TRIM(full_name)) AS display_name  -- '  alice johnson  ' → 'ALICE JOHNSON'
FROM   customers;

-- ============================================================
-- SUBSTRING: extract just the domain from an email address.
-- We know the '@' is always there — find where it is, then
-- grab everything after it.
--
-- INSTR(string, search) returns the position of the first
-- match. We add 1 to skip past the '@' itself.
-- ============================================================
SELECT
    email,
    SUBSTRING(
        email,
        INSTR(email, '@') + 1,          -- start one char AFTER the '@'
        LENGTH(email)                    -- grab the rest (generous upper bound)
    ) AS email_domain                   -- 'alice@example.com' → 'example.com'
FROM   customers;

-- ============================================================
-- CONCAT: build a full display label from separate columns.
-- Notice we add a literal string ' — ' between city and name.
-- ============================================================
SELECT
    CONCAT(
        TRIM(UPPER(city)),      -- 'NEW YORK'
        ' | ',                  -- literal separator
        TRIM(full_name)         -- 'alice johnson'
    ) AS customer_label
FROM   customers;

-- PostgreSQL / standard SQL alternative (same result):
-- SELECT TRIM(UPPER(city)) || ' | ' || TRIM(full_name) AS customer_label
-- FROM   customers;

-- ============================================================
-- REPLACE: the marketing team re-branded 'example.com' to
-- 'mycompany.com' — fix all the email addresses in one shot.
-- ============================================================
SELECT
    email,
    REPLACE(
        LOWER(email),           -- normalise case first so the match is reliable
        'example.com',          -- the old text to find
        'mycompany.com'         -- the new text to put in its place
    ) AS updated_email
FROM   customers;

-- ============================================================
-- REAL UPDATE using REPLACE (not just a SELECT preview):
-- Uncomment only when you're ready — always preview first!
-- ============================================================
-- UPDATE customers
-- SET    email = REPLACE(LOWER(email), 'example.com', 'mycompany.com')
-- WHERE  email LIKE '%example.com%';  -- safety net: only touch matching rows

-- ============================================================
-- SUBSTRING to extract area code from a stored phone number
-- Assumes format: '212-555-0100' — area code is always first 3 chars
-- ============================================================
SELECT
    '212-555-0100'            AS raw_phone,
    SUBSTRING('212-555-0100', 1, 3) AS area_code;  -- '212'