SQL String Functions: A Step-by-Step Guide for Beginners

By the end of this guide, you will be able to take a messy text column — names with inconsistent casing, phone numbers formatted five different ways, full names crammed into a single field — and reshape it into something clean and queryable using SQL string functions. You will know which function to reach for, what it returns when the input is NULL or empty, and where a common assumption about these functions breaks down.

Text handling is where a lot of beginner SQL friction shows up, because real data rarely arrives tidy. This guide walks through the functions you will use most often, builds them up one step at a time, and flags the edges where they behave in ways that surprise people.

Myth vs. Reality: How Beginners Think About String Functions

Before the functions themselves, it helps to clear up a few assumptions that cause trouble early on. Each of the following is a belief that seems reasonable until you hit a specific case that contradicts it.

Myth 1: “String functions change the data in the table.”

Reality: String functions return a new value for the current query result. They do not modify the stored data unless you explicitly write an UPDATE statement that assigns the function’s output back to the column. Running SELECT UPPER(first_name) FROM customers leaves the underlying table untouched. Every transformation you see exists only in that result set.

This distinction matters because it means you can experiment freely. Run a transformation, look at the output, compare it to the original, and decide whether it is correct before committing anything. The source data stays safe until you deliberately write back to it.

Myth 2: “UPPER and LOWER are enough to fix inconsistent casing.”

Reality: Casing functions normalize everything to one extreme, which is rarely what clean data looks like. UPPER('maria garcia') gives you MARIA GARCIA, and LOWER gives you maria garcia. Neither produces Maria Garcia. Title casing — capitalizing the first letter of each word — usually requires combining several functions, since most databases do not ship a single INITCAP-style function across all engines (PostgreSQL and Oracle have INITCAP; MySQL and SQL Server do not provide it natively).

Myth 3: “TRIM removes all the extra spaces.”

Reality: TRIM removes leading and trailing spaces by default. It does nothing to spaces in the middle. TRIM(' hello world ') returns hello world — the internal run of spaces is preserved. If you need to collapse multiple internal spaces into one, that is a separate operation, and the approach differs by database.

Myth 4: “SUBSTRING starts counting at zero.”

Reality: SQL string positions are one-indexed in essentially every major database. The first character is position 1, not 0. SUBSTRING('hello', 2, 3) returns ell, not llo. If you carry a zero-indexing habit from another language, this is the assumption that trips you up most often.

The Core Functions, One at a Time

With those misconceptions set aside, here is a practical walkthrough of the functions you will reach for most often. Each builds on the last.

Setup: A Sample Table to Work With

To make the examples concrete, picture a table like this:

CREATE TABLE contacts (
  id          INTEGER PRIMARY KEY,
  full_name   VARCHAR(100),
  email       VARCHAR(150),
  phone       VARCHAR(30)
);

And a few rows that represent the kind of messiness real data contains:

INSERT INTO contacts (id, full_name, email, phone) VALUES
  (1, '  maria garcia  ', '[email protected] ', '555-1234'),
  (2, 'JOHN  SMITH', '[email protected]', '(555) 987-6543'),
  (3, 'Aisha Bello', NULL, '555.555.0199');

Notice the deliberate imperfections: leading and trailing spaces, mixed casing, an empty field, and three different phone number formats. This is representative of what shows up when data is entered by hand or pulled from multiple systems.

LENGTH: Counting Characters

LENGTH (or LEN in SQL Server) returns the number of characters in a string.

SELECT full_name, LENGTH(full_name) AS name_length
FROM contacts;

One thing to know: LENGTH counts characters, which is not always the same as byte count. In most databases, LENGTH counts characters and OCTET_LENGTH counts bytes. For plain ASCII text the two are identical, but for accented characters or non-Latin scripts they diverge. If you are sizing a target column, check which measure your database’s LENGTH returns.

TRIM, LTRIM, RTRIM: Removing Unwanted Whitespace

These strip spaces from the ends of a string. TRIM removes both sides, LTRIM removes only leading spaces, and RTRIM removes only trailing spaces.

SELECT
  full_name,
  TRIM(full_name) AS cleaned_name,
  LENGTH(TRIM(full_name)) AS cleaned_length
FROM contacts;

For the row containing ' maria garcia ', the cleaned name becomes maria garcia and the length drops from 17 to 12. This is usually the first step in any text cleaning routine, because leading and trailing whitespace causes silent comparison failures. 'maria garcia' = ' maria garcia ' evaluates to false, and if you have ever wondered why a lookup is not matching, stray whitespace is one of the first things to check.

Most databases also support TRIM(LEADING 'x' FROM column) syntax to strip a specific character rather than just spaces, though the exact support varies.

UPPER and LOWER: Normalizing Casing

These convert a string to all uppercase or all lowercase.

SELECT
  email,
  LOWER(TRIM(email)) AS normalized_email
FROM contacts;

Applied to '[email protected] ', this returns [email protected]. Normalizing casing before comparisons is standard practice for email addresses, usernames, and any field where the user’s capitalization choices should not affect matching. It is also the foundation for case-insensitive joins.

CONCAT: Joining Strings Together

CONCAT combines two or more strings into one.

SELECT
  CONCAT('Contact: ', TRIM(full_name)) AS labeled_name
FROM contacts;

An important behavior to understand: in most databases, CONCAT treats NULL as an empty string rather than propagating it. CONCAT('Hello, ', NULL) returns 'Hello, ' in MySQL and PostgreSQL. This is convenient but can hide missing data. If you want NULL to propagate and flag the problem, use the || operator instead where supported (PostgreSQL, Oracle, SQLite), which returns NULL when any operand is NULL.

-- PostgreSQL / SQLite: NULL propagates
SELECT 'Contact: ' || full_name AS labeled_name
FROM contacts;

Choosing between CONCAT and || is a real trade-off. CONCAT is safer against unexpected NULLs breaking your output, but || surfaces data quality problems rather than masking them. Pick deliberately based on whether a missing value should be visible or absorbed.

SUBSTRING: Extracting Part of a String

SUBSTRING(string, start, length) pulls out a section of the string, beginning at the one-indexed start position.

SELECT
  phone,
  SUBSTRING(phone, 1, 3) AS area_code
FROM contacts;

On '555-1234' this returns 555. On '(555) 987-6543' it returns (55 — because the substring is positional, not semantic. This is the key limitation of SUBSTRING: it slices by position, so it only works reliably when the data has a fixed, known layout. When formats vary, you need a different tool, which brings us to the next function.

REPLACE: Swapping Characters or Substrings

REPLACE(string, from_substring, to_substring) substitutes every occurrence of one substring with another.

SELECT
  phone,
  REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '') AS digits_only
FROM contacts;

Nested REPLACE calls let you strip several different characters in one expression. Applied to '(555) 987-6543', this returns '555 9876543' — the parentheses and hyphens are gone, but a space remains. To get true digits-only output you would need to also handle the space, and the cleanest way to do that depends on your database. SQL Server 2017 and later has TRANSLATE, which maps multiple characters in one pass, and MySQL 8.0 has REGEXP_REPLACE, which handles arbitrary patterns.

The nested-REPLACE approach works but does not scale well. If you find yourself nesting five or six of them to normalize phone numbers or IDs, that is a signal to reach for a regular-expression function or to clean the data upstream before it lands in the database.

A Worked End-to-End Example

Putting several functions together, here is a query that takes a raw contact row and produces a cleaned version with a normalized phone number and a properly cased name.

SELECT
  id,
  -- Trim outer whitespace, then remove internal double spaces
  REPLACE(TRIM(full_name), '  ', ' ') AS clean_name,
  -- Lowercase and trim the email; NULL stays NULL
  LOWER(TRIM(email)) AS clean_email,
  -- Strip common separators to get a digit string
  REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', '') AS phone_digits
FROM contacts;

Run through the sample rows, this produces:

A few things to verify in this result. Row 1’s name is trimmed but not title-cased — the casing function only handles extremes, so maria garcia stays lowercase. Row 2’s internal double space became a single space, but the name is still uppercase. Row 3’s email is NULL because the original was NULL and both LOWER and TRIM propagate NULL. Row 3’s phone still contains dots, because the REPLACE chain only handled hyphens and parentheses.

This is the verification step that matters: run the query, read the output row by row, and confirm each transformation did what you intended. The output above shows three remaining problems that a follow-up query would need to address.

Handling NULL and Empty Strings

NULL behavior in string functions is worth its own section because it is where beginner queries most often break in ways that are hard to diagnose.

Most string functions propagate NULL: UPPER(NULL) returns NULL, LENGTH(NULL) returns NULL, SUBSTRING(NULL, 1, 3) returns NULL. This is consistent and predictable. The exception is CONCAT in several databases, which treats NULL as an empty string. The inconsistency between CONCAT and || is a common source of confusion when moving code between database engines.

Use COALESCE to substitute a default value where a NULL would otherwise propagate:

SELECT
  COALESCE(LOWER(TRIM(email)), 'no-email@placeholder') AS safe_email
FROM contacts;

This is useful in reporting, where a blank cell is often worse than an explicit placeholder. But be deliberate about it. Substituting a placeholder hides the fact that the source data was missing, and if that missing data matters, you may want the NULL to remain visible so someone notices and fixes it upstream.

The other edge case is the empty string, which is distinct from NULL. LENGTH('') returns 0, while LENGTH(NULL) returns NULL. A column can contain empty strings and NULLs side by side, and if you only check for one, you will miss the other. A robust filter is WHERE email IS NULL OR TRIM(email) = ''.

When NOT to Use String Functions

String functions are powerful but they are not always the right tool, and knowing when to avoid them saves real trouble.

Do not use them to clean data that will be cleaned repeatedly. If a report depends on trimming and normalizing a column every time it runs, that work should happen once, upstream, at the point where the data is loaded. Repeating the same transformation across dozens of queries is a maintenance problem: when the cleaning logic changes, every query has to change too. A staging table or a view that materializes the cleaned values is usually the better design.

Do not use them to parse structured data out of a blob. If a single column contains something like a JSON string, a delimited list, or a serialized object, slicing it with SUBSTRING and REPLACE is fragile. Modern databases ship native JSON functions, and they handle escaping, nested structures, and malformed input far more reliably than string slicing ever will.

Do not expect them to be free at scale. Applying string functions to a column inside a WHERE clause can prevent the database from using an index on that column, because the function has to be evaluated for every row before the comparison happens. WHERE LOWER(email) = '[email protected]' may scan the whole table where WHERE email = '[email protected]' on a normalized column could use an index. The fix is to store the normalized form in its own indexed column.

A Practical Starting Checklist

When you sit down to clean a text column, the following order handles most cases:

1. Trim first. Leading and trailing whitespace breaks comparisons and looks invisible in output, so removing it early prevents confusion later.
2. Normalize casing next. Decide whether the field should be all-lowercase (for matching), all-uppercase (for display codes), or title-cased (for names), and apply the appropriate function.
3. Inspect the distinct values. Before writing a REPLACE or SUBSTRING chain, run SELECT DISTINCT on the column to see every format that exists. Guessing at patterns is how you end up with a cleaning query that quietly fails on the rows you did not think to check.
4. Verify against a sample. Run the transformation on a handful of rows and read the output carefully, the way the worked example above does.
5. Decide whether to fix it in the query or upstream. If the cleaning is temporary or exploratory, keep it in the query. If it is permanent, move it into the load process and add a constraint so the bad data cannot enter in the first place.

Which text column in your data is giving you trouble, and what specifically is wrong with it — inconsistent casing, mixed formats, embedded separators, or missing values? Describe the shape of the problem and the functions above will map onto a solution.