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.

What You'll Learn

By the end of this lesson you will write one query that takes a column of customer names and product codes typed in by four different people, and returns them in a single consistent shape — trimmed, cased the same way, with the parts you need cut out of them. The skill underneath is narrower than it looks and it is the one worth practising: knowing exactly what each function does to a value it was not designed for, because these functions almost never raise an error. Give one the wrong input and it returns something, and that something goes straight into your report.

This lesson feeds the capstone rather than owning a milestone of its own. The warehouse's product codes and customer names arrive from imports that disagree about casing and whitespace, and every join and grouping that follows only works if two spellings of the same thing have first been made into one. You arrive here able to select, filter and sort rows (Filtering and Sorting); what is new is changing the values themselves on their way out of the database.

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
ORDER BY id;

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.

Both of those functions have a limit that the sentence above hides, and neither limit announces itself: nothing raises an error, you simply get back a value you did not mean. The next two blocks are where you find out what each one does to input it was not designed for — first a number handed to a function that expects text, then a non-English name handed to the case folders.

CREATE TABLE codes (id INTEGER PRIMARY KEY, sku INTEGER NOT NULL, label TEXT NOT NULL);
INSERT INTO codes VALUES (1, 4299, 'Mouse'),(2, 87, 'Pad');
SELECT sku, LENGTH(sku) AS len_of_number, LENGTH(label) AS len_of_text, typeof(sku) AS stored_as
FROM codes ORDER BY sku;

SELECT UPPER('straße') AS german,
     UPPER('café')   AS french,
     UPPER('mouse')  AS plain,
     LOWER('ÉCOLE')  AS lowered;

To state both limits plainly now that you have worked them out. LENGTH() converts a number to text before counting, so it reports the digit count rather than refusing. And UPPER() and LOWER() change only the 26 ASCII letters — UPPER('café') is CAFé and UPPER('straße') is STRAßE — so case folding is not a reliable way to compare names in most of the world's languages.

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
ORDER BY id;

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 spaces — spaces specifically, not whitespace in general: a leading tab or newline survives TRIM() untouched, because the default character set it strips is the single space character. 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,
    -- Strip the four formatting characters this data actually uses
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(phone, '-', ''), ' ', ''), '(', ''), ')', ''), '.', '') AS digits_only,
    LENGTH(TRIM(name)) AS name_length
FROM raw_employees
ORDER BY id;

Count the REPLACE() calls and then count the characters in the data: there are five calls because this import used five different separators, and the fifth one — the period in 555.901.2345 — is the reason this pattern is worth being suspicious of. Nested REPLACE() removes exactly the characters you listed and nothing else, so the moment a new separator appears in the data the column silently stops being digits-only while still being called digits_only. A column name is a claim, and this one is only true for as long as nobody invents a new way to type a phone number.

Look at the second row of output too. TRIM() removed nothing from the middle of John Doe, because trimming is an operation on the two ends of a string — the three interior spaces are still there and clean_name is still ten characters long. If you need interior whitespace collapsed, that is REPLACE()'s job, not TRIM()'s.

TRIM() without a second argument removes spaces. You can also pass a specific character to trim: TRIM(name, '.') removes leading and trailing periods.

Cleaning Is Not the Same as Safety

One warning before you start reaching for these functions on data that came from outside. Every value in this lesson arrived from somewhere a person typed it, and it is tempting to read a chain of TRIM and REPLACE as making such a value safe to put in a query. It does not. Cleaning changes what a value says; it does nothing about whether that value can become part of the statement's syntax. A value still goes into a query as a bound parameter — the ? placeholder you met in Filtering and Sorting, with the value handed to the database driver separately — and that is what makes it a value rather than code, whatever characters it contains.

The same caveats travel here. A placeholder stands for a value and never for an identifier, so if the column you clean or sort by is itself chosen by the user, no amount of escaping helps: match the incoming name against a fixed allow-list of column names you wrote yourself. And as before, none of this is runnable on this page — the Run button executes a literal script with no program around it, so binding is a shape to recognise rather than something to execute here.

Combining Strings with Concatenation

The || operator joins strings together. It is the concatenation operator SQLite uses, and the one the SQL standard defines; note that some engines also or instead offer a CONCAT() function, so || is not universally portable and the safest habit is to check what the engine in front of you accepts. 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.

One warning about concatenating a number, because it is the commonest way a receipt line comes out wrong. || converts the number to text using SQLite's own rules, which drop trailing zeros from a float: 'Total: ' || 499.00 produces Total: 499.0, not Total: 499.00. When a number is going into text that a person will read, format it explicitly with printf('%.2f', amount), which produces 499.00 exactly.

Cleaning in Stages

The functions in this lesson compose, and the order you compose them in is not free. Each stage below takes the output of the one above it.

CREATE TABLE raw (id INTEGER PRIMARY KEY, entry TEXT NOT NULL);
INSERT INTO raw VALUES (1,'  ELC-mon-0042 '),(2,'frn-CHR-0008');

Two raw entries are already loaded, one padded with spaces and both inconsistently cased. CREATE TABLE ... AS SELECT builds and fills a table from a query in one statement.

Two Values That Look Different and Are the Same Customer

CREATE TABLE signups (id INTEGER PRIMARY KEY, email TEXT NOT NULL);
INSERT INTO signups VALUES
(1,'ada@example.com'),
(2,'  ada@example.com'),
(3,'ADA@Example.com  '),
(4,'bo@example.com'),
(5,'Bo@Example.com');
SELECT COUNT(DISTINCT LOWER(email)) AS distinct_people FROM signups;

5 row(s) affected
distinct_people
---------------
2

Try It Yourself

The build below is a receipt formatter. The starter runs and prints a column that is trimmed and nothing else; the rest of the cleaning is yours.

CREATE TABLE purchases (id INTEGER PRIMARY KEY, customer TEXT, product_code TEXT, cents INTEGER);
INSERT INTO purchases VALUES
(1,'alice JOHNSON','  elc-mon-0042  ',34999),
(2,'BOB smith','ELC-KBD-0117',8999),
(3,'carol williams','  FRN-CHR-0008',49900);
-- TODO 1: make clean_code the trimmed AND uppercased product code.
--         Row 1 must read ELC-MON-0042.
-- TODO 2: add a category column holding the first three characters of the clean code.
--         Row 1 is ELC, row 3 is FRN.
-- TODO 3: add a display_name column: the customer name with its first letter
--         uppercased and every following character lowercased. Row 2 is Bob smith
--         because only the very first letter is touched, not the first letter of each word.
-- TODO 4: add a price column showing cents as a two-decimal string.
--         34999 cents must read 349.99, and 49900 must read 499.00, not 499.0.
SELECT
id,
TRIM(product_code) AS clean_code
FROM purchases
ORDER BY id;

3 row(s) affected
id | clean_code | category | display_name | price
-------------------------------------------------
1 | ELC-MON-0042 | ELC | Alice johnson | 349.99
2 | ELC-KBD-0117 | ELC | Bob smith | 89.99
3 | FRN-CHR-0008 | FRN | Carol williams | 499.00

Once it passes, try two variations and predict each before running. First, swap the nesting in TODO 1 so it reads TRIM(UPPER(product_code)) instead of UPPER(TRIM(product_code)) — predict whether the output changes at all, and be able to say why these two commute when many function pairs do not. Second, change the price column to use ROUND(cents / 100.0, 2) instead of printf — predict what row 3 prints, then confirm that it reads 499.0 and that a rounded float cannot be relied on to keep its trailing zero.

CREATE TABLE people (full_name TEXT NOT NULL);
INSERT INTO people VALUES ('Ada Lovelace'),('Grace Hopper'),('Prince');

-- shape A slices up to the first space; shape B removes the rest of the name
SELECT full_name,
substr(full_name, 1, instr(full_name, ' ') - 1) AS shape_a,
replace(full_name, ' ' || substr(full_name, instr(full_name, ' ') + 1), '') AS shape_b
FROM people ORDER BY full_name;

Key Takeaways

• UPPER() and LOWER() normalize casing for the 26 ASCII letters only — accented and non-Latin characters pass through unchanged, so case folding alone does not make international names comparable
• LENGTH() counts characters in a string, and counts the digits of a number handed to it
• 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, and returns 0 if not found — a value that flows on into whatever you feed it to
• TRIM() strips leading and trailing spaces, not tabs or newlines and not interior 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: Normalize text on both sides before comparing it — WHERE LOWER(TRIM(email)) = 'user@example.com' matches whatever casing and surrounding spaces the address was stored with. Note that LOWER() alone is not enough, as the debug block above shows: a stray leading space survives case folding and the row quietly fails to match. 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.

Next Steps

Now that you can manipulate text in SQL, the next lesson tackles date and time functions — the tools you need to filter by time ranges, calculate intervals, and build time-based reports.