String Functions

Real-world data is messy. Names have inconsistent casing, phone numbers include dashes and spaces, addresses get entered in different formats, and email addresses need validation. SQL string functions give you the tools to clean, transform, and extract meaning from text data — all inside your queries, without needing to post-process results in application code.

In this lesson you'll learn the most essential string functions available in SQL and how to combine them to solve practical data problems.

Changing Case and Measuring Length

The simplest string functions change the case of text or measure how long it is. These are surprisingly useful when you need to normalize data before comparison or display.

-- Set up a contacts table with inconsistent casing
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

INSERT INTO contacts VALUES
    (1, 'alice', 'JOHNSON', 'alice@example.com'),
    (2, 'BOB', 'smith', 'BOB@EXAMPLE.COM'),
    (3, 'Carol', 'Williams', 'carol@example.com'),
    (4, 'david', 'BROWN', 'David@Example.Com');

-- Normalize names and check email lengths
SELECT
    id,
    UPPER(first_name) AS first_upper,
    LOWER(last_name) AS last_lower,
    LOWER(email) AS normalized_email,
    LENGTH(email) AS email_length
FROM contacts;

UPPER() converts all characters to uppercase, LOWER() converts to lowercase, and LENGTH() returns the number of characters in a string. Notice how LOWER(email) normalizes all those inconsistently-entered email addresses into a consistent format for storage or comparison.

Extracting Parts of a String

SUBSTR() lets you cut out a portion of a string by specifying a start position and length. INSTR() finds where a substring appears within a string. Together they're powerful for parsing structured text like phone numbers, codes, or dates stored as strings.

-- Product codes follow the format: CAT-SUBCAT-NNNN
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    code TEXT NOT NULL,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products VALUES
    (1, 'ELC-MON-0042', 'Monitor 24"', 349.99),
    (2, 'ELC-KBD-0117', 'Mechanical Keyboard', 89.99),
    (3, 'FRN-CHR-0008', 'Ergonomic Chair', 499.00),
    (4, 'FRN-DSK-0031', 'Standing Desk', 799.00),
    (5, 'ELC-MSE-0095', 'Wireless Mouse', 44.99);

-- Extract category, subcategory, and numeric ID from the code
SELECT
    code,
    name,
    SUBSTR(code, 1, 3) AS category,
    SUBSTR(code, 5, 3) AS subcategory,
    SUBSTR(code, 9) AS item_number,
    INSTR(code, '-') AS first_dash_position
FROM products;

SUBSTR(string, start, length) takes three arguments: the string, the 1-based start index, and an optional length. If you omit the length, it returns everything from the start position to the end of the string. INSTR(string, pattern) returns the position of the first match, or 0 if the pattern isn't found.

Cleaning Strings with TRIM and REPLACE

Data imported from spreadsheets, user forms, or external systems often has extra whitespace or needs certain characters swapped out. TRIM() removes leading and trailing whitespace. REPLACE() substitutes every occurrence of one substring with another.

-- Raw import data with whitespace and formatting issues
CREATE TABLE raw_employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    phone TEXT,
    department TEXT
);

INSERT INTO raw_employees VALUES
    (1, '  Sarah Connor  ', '555-867-5309', '  Engineering  '),
    (2, 'John   Doe', '(555) 234-5678', 'Marketing'),
    (3, '  Maria Garcia', '555.901.2345', 'Engineering  '),
    (4, 'James Wilson  ', '555-444-9988', '  HR');

-- Clean up whitespace and normalize phone numbers
SELECT
    id,
    TRIM(name) AS clean_name,
    TRIM(department) AS clean_department,
    -- Remove all non-digit formatting from phone numbers
    REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', ''), ')', '') AS digits_only,
    LENGTH(TRIM(name)) AS name_length
FROM raw_employees;

TRIM() without a second argument removes whitespace. You can also pass a specific character to trim: TRIM(name, '.') removes leading and trailing periods. REPLACE(string, old, new) replaces every match — notice how nesting multiple REPLACE() calls lets you strip several different characters in one pass.

Combining Strings with Concatenation

The || operator joins strings together. This is standard SQL concatenation and works across most databases. Use it to build full names from parts, construct URLs, format output, or combine columns into a single readable value.

-- Employee directory with separate name fields
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    department TEXT NOT NULL,
    hire_year INTEGER NOT NULL
);

INSERT INTO employees VALUES
    (1, 'Alice', 'Johnson', 'Engineering', 2019),
    (2, 'Bob', 'Smith', 'Marketing', 2021),
    (3, 'Carol', 'Williams', 'Engineering', 2018),
    (4, 'David', 'Brown', 'HR', 2022),
    (5, 'Eva', 'Martinez', 'Marketing', 2020);

-- Build formatted output strings
SELECT
    first_name || ' ' || last_name AS full_name,
    UPPER(SUBSTR(first_name, 1, 1)) || '. ' || last_name AS formal_name,
    LOWER(first_name) || '.' || LOWER(last_name) || '@company.com' AS email,
    'Joined ' || hire_year || ' (' || department || ')' AS summary
FROM employees
ORDER BY last_name;

You can chain || as many times as needed and mix in literal strings (in single quotes), numbers, and other expressions. Notice how this example also nests SUBSTR() and UPPER() inside concatenation to build a proper initial.

Try It Yourself

A retail company stores customer purchase history with product codes, but the codes have extra whitespace and mixed casing. Write queries to clean the data and extract useful information.

CREATE TABLE purchases (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    product_code TEXT,
    amount REAL
);

INSERT INTO purchases VALUES
    (1, 'alice JOHNSON', '  elc-mon-0042  ', 349.99),
    (2, 'BOB smith', 'ELC-KBD-0117', 89.99),
    (3, 'carol williams', '  FRN-CHR-0008', 499.00),
    (4, 'DAVID brown  ', 'frn-dsk-0031  ', 799.00),
    (5, 'Eva Martinez', '  ELC-MSE-0095  ', 44.99);

-- Challenge 1: Display customer names in Title Case (first letter upper, rest lower)
-- Hint: combine UPPER, LOWER, SUBSTR, and INSTR to find the space between names

-- Challenge 2: Normalize product codes (trim whitespace, uppercase)
-- Then extract just the category prefix (first 3 characters)

-- Challenge 3: Build a receipt line like:
-- "Alice Johnson purchased ELC-MON-0042 for $349.99"

-- Start here:
SELECT
    id,
    TRIM(product_code) AS clean_code
FROM purchases;

Key Takeaways

UPPER() and LOWER() normalize text casing, making case-insensitive comparisons reliable
LENGTH() counts characters in a string, useful for validation and filtering
SUBSTR(string, start, length) extracts a portion of a string; omit length to go to the end
INSTR(string, pattern) finds where a substring first appears (returns 0 if not found)
TRIM() strips leading and trailing whitespace; pass a second argument to trim a specific character
REPLACE(string, old, new) substitutes every occurrence of a substring — chain calls to handle multiple patterns
The || operator concatenates strings and can be nested with other functions for complex formatting
String functions can be combined in a single expression to perform multi-step transformations in one query

Pro Tip: Always normalize text data at query time using LOWER() or UPPER() before comparing values — WHERE LOWER(email) = 'user@example.com' will match regardless of how the email was originally stored. For high-traffic tables, consider storing data already normalized (all lowercase emails, trimmed names) to keep indexes effective and avoid per-row function evaluation on every query.