SQL string functions: cleaning, combining, and matching text correctly

Text is rarely as clean as it looks

Real-world databases contain a large amount of text. Customer names, email addresses, product names, cities, status codes, reference numbers, and descriptions are all commonly stored as character data. Consider the following customers table:

Text values may need to be converted to a consistent case, combined into a new value, split into smaller parts, cleaned of unwanted spaces, modified, or searched using patterns. SQL provides string functions and operators for these tasks.

Exact function names and syntax vary somewhat between database systems. The examples below use PostgreSQL-style SQL where syntax differs.

Changing text case with UPPER and LOWER

Text may arrive in different forms. Our email column contains values such as ASHA@EXAMPLE.COM, ben@example.com, and carla@example.com. The values represent email addresses, but their capitalization is inconsistent. We can convert text to lowercase:

SELECT
    customer_id,
    email,
    LOWER(email) AS normalized_email
FROM customers
ORDER BY customer_id;

LOWER changes alphabetic characters to lowercase. Similarly, UPPER(city) converts text to uppercase, so Bengaluru, London, and Dubai become BENGALURU, LONDON, and DUBAI. These functions are useful when text needs a consistent presentation or normalization step.

Removing unwanted spaces with TRIM

Text imported from files, APIs, forms, and older systems often contains extra spaces. Visually, ' esha@example.com ' may look almost identical to 'esha@example.com', but they are different strings. Use TRIM to remove spaces from the beginning and end:

SELECT
    customer_id,
    email,
    TRIM(email) AS cleaned_email
FROM customers
ORDER BY customer_id;

Before:  ' esha@example.com '
After:   'esha@example.com'

Combining cleaning steps

Often text needs more than one transformation. Suppose we want every email without leading or trailing spaces and in lowercase. We can combine functions:

SELECT
    customer_id,
    email,
    LOWER(TRIM(email)) AS normalized_email
FROM customers
ORDER BY customer_id;

For Asha, ASHA@EXAMPLE.COM becomes asha@example.com. For Esha, ' esha@example.com ' becomes esha@example.com. SQL evaluates the inner function first, TRIM(email), and then applies LOWER(...) to that result.

LTRIM and RTRIM

Sometimes you want to remove spaces from only one side. Common functions include LTRIM(text) and RTRIM(text):

LTRIM    removes leading spaces
RTRIM    removes trailing spaces
TRIM     removes both

For most data-cleaning situations involving accidental surrounding whitespace, TRIM is the natural starting point.

The equality check that misses a value that looks identical

Suppose the requirement is find the customer with email esha@example.com. Someone writes WHERE email = 'esha@example.com'. The query may return no Esha row. Why? Her stored value is ' esha@example.com ', while the filter contains 'esha@example.com'. These strings are not identical.

-- Option A
WHERE email = 'esha@example.com'

-- Option B
WHERE LOWER(email) = 'esha@example.com'

-- Option C
WHERE LOWER(TRIM(email)) = 'esha@example.com'

-- Option D
WHERE email LIKE '%'

Cleaning at query time is not always the final solution

Expressions such as LOWER(TRIM(email)) are useful when working with imperfect data. But if every query must repeatedly clean the same column, the underlying data model or ingestion process may need attention. Email normalization, for example, may be better enforced when the value enters the system. Query-time cleaning is useful, but it should not automatically become a substitute for reliable data quality.

Combining text with concatenation

Sometimes we need to create a new text value from several columns. Suppose customer names were stored separately:

We want Asha Rao, Ben Thomas, and Carla Shah. In PostgreSQL, the || operator can concatenate strings:

SELECT
    customer_id,
    first_name || ' ' || last_name AS full_name
FROM customers;

The expression has three pieces: first_name, a space, and last_name. So Asha, ' ', and Rao becomes Asha Rao. Many database systems also provide a CONCAT function, although exact behaviour and syntax can differ.

Concatenation can create business-friendly values

Suppose we want a label containing both the customer and city:

SELECT
    customer_name || ' - ' || city AS customer_location
FROM customers;

The combined value does not need to exist as a stored column. It is created for the query result.

Gotcha: NULL can affect concatenation

Suppose a customer’s last name is NULL. With concatenation operators in many systems, an expression involving NULL can itself become NULL. For example, first_name || ' ' || last_name may produce NULL when last_name is NULL. If the business requirement says a missing last name should simply be omitted, handle that explicitly:

first_name || COALESCE(' ' || last_name, '')

Extracting part of a string with SUBSTRING

Sometimes one text value contains several useful pieces of information. Look at the referral codes REF-IND-101, REF-UK-205, REF-UAE-310. Suppose the first three characters identify the code type, REF. We can extract part of the value using SUBSTRING:

SELECT
    referral_code,
    SUBSTRING(referral_code FROM 1 FOR 3) AS code_type
FROM customers;

The expression SUBSTRING(referral_code FROM 1 FOR 3) means start at character 1 and return 3 characters.

Extracting a known segment

Suppose our codes always follow REF-XXX-999 and we want part of the country segment. For REF-IND-101 we might extract characters beginning after REF-. But notice a problem: IND, UK, UAE, and SG do not all have the same length. Fixed-position substring logic works best when the text format has fixed positions. If the structure varies, delimiter-aware functions or pattern-based extraction may be more appropriate depending on the database.

Measuring text with LENGTH

A basic string function that is useful for validation is LENGTH:

SELECT
    customer_name,
    LENGTH(customer_name) AS name_length
FROM customers;

This can help answer questions such as which codes have unexpected lengths, which values are empty, which descriptions exceed an expected size, or whether a source system sent malformed identifiers. For example, WHERE LENGTH(TRIM(referral_code)) <> 11 could be useful if the business specification requires every code to contain exactly eleven characters. The rule must come from the data format, not from the function itself.

Replacing text with REPLACE

REPLACE substitutes one piece of text with another. Suppose a report needs REF/IND/101 instead of REF-IND-101:

SELECT
    referral_code,
    REPLACE(referral_code, '-', '/') AS formatted_code
FROM customers;

REPLACE is useful for transformations such as changing delimiters, removing known characters, standardizing simple formats, and replacing known tokens. Replacing text with an empty string effectively removes it: REPLACE(referral_code, '-', '') turns REF-IND-101 into REFIND101. This can be useful when punctuation is only presentation formatting, but if punctuation carries meaning, removing it changes the data rather than merely cleaning it.

Pattern matching with LIKE

Sometimes we do not know the complete value we want to match. Suppose we want every customer whose name begins with A. Equality is too strict: WHERE customer_name = 'A' asks for exactly A. Instead, use LIKE with a wildcard:

SELECT
    customer_id,
    customer_name
FROM customers
WHERE customer_name LIKE 'A%';

The % wildcard means zero or more characters. So A% matches values such as Asha Rao, Amit, A, and Ananya Singh.

The two common LIKE wildcards

% matches zero or more characters, so LIKE 'A%' means begins with A, LIKE '%pur' means ends with pur, and LIKE '%example%' means contains example anywhere. _ matches exactly one character, so LIKE 'REF-_ND-%' requires one character in the position represented by _.

%    any number of characters
_    exactly one character

Matching text anywhere

Suppose we need customers whose email contains example.com:

SELECT
    customer_name,
    email
FROM customers
WHERE email LIKE '%example.com%';

The wildcard before the text allows characters before example.com, and the wildcard after allows characters after it.

Case sensitivity depends on the database

Do not assume WHERE customer_name LIKE 'a%' will always match Asha Rao. Case-sensitive behaviour can depend on the database and collation. In PostgreSQL, ILIKE provides case-insensitive pattern matching (WHERE customer_name ILIKE 'a%'). A more portable conceptual approach is to normalize the text: WHERE LOWER(customer_name) LIKE 'a%'. The exact implementation should follow the database you are using.

Gotcha: wildcards are data when used with equality

WHERE customer_name = 'A%' does not mean name starts with A. With equality, % is just a percent character. Pattern behaviour comes from LIKE, so WHERE customer_name LIKE 'A%' is the intended pattern match.

Gotcha: % can match more than you intended

Suppose you want referral codes for India, REF-IND-101 and REF-IND-415. You might write WHERE referral_code LIKE '%IND%'. This works for the current sample, but it means match IND anywhere in the value. If some future code contains REF-OTHER-IND-999, it would also match. If the code format is structured, the pattern should reflect that structure as precisely as possible:

WHERE referral_code LIKE 'REF-IND-%'

This communicates REF-, then IND, then another -, then the remaining code. A narrower pattern is easier to reason about.

Combining string functions

Real text-cleaning problems often involve several operations together. Suppose we want to normalize email addresses by removing leading and trailing spaces and converting the value to lowercase, then keep only normalized addresses from example.com:

SELECT
    customer_id,
    customer_name,
    LOWER(TRIM(email)) AS normalized_email
FROM customers
WHERE LOWER(TRIM(email)) LIKE '%@example.com';

The same expression appears in both the output and the filter. This is valid, but repeated transformations can make larger queries harder to read. Later, intermediate results such as CTEs can help name and reuse cleaned values:

WITH normalized_customers AS (
    SELECT
        customer_id,
        customer_name,
        LOWER(TRIM(email)) AS email
    FROM customers
)
SELECT
    customer_id,
    customer_name,
    email
FROM normalized_customers
WHERE email LIKE '%@example.com';

This separates normalizing the text from using the normalized text.

Text cleaning can change matching behaviour

Consider two values, 'Ben Thomas' and ' ben thomas '. A direct equality comparison treats them as different. After LOWER(TRIM(...)), both may become ben thomas. This can be exactly what the business requires, but it can also accidentally merge values that the business considers different.

How to spot text-processing problems

Look for text operations when:

Watch for common mistakes such as:

• Using LOWER but forgetting surrounding whitespace.
• Assuming empty strings and NULL are the same.
• Using fixed substring positions on variable-length data.
• Using REPLACE without considering other occurrences of the text.
• Using = with % and expecting wildcard matching.
• Using a pattern that is broader than the business rule.
• Assuming LIKE has the same case behaviour across every database.

Ask what transformation the requirement actually needs

One more prediction

Consider these email values. Requirement: return customers whose normalized email belongs to example.com. Capitalization and surrounding spaces should not affect matching. Which condition best expresses the requirement?

-- Option A
WHERE email LIKE '%@example.com'

-- Option B
WHERE LOWER(email) LIKE '%@example.com'

-- Option C
WHERE LOWER(TRIM(email)) LIKE '%@example.com'

-- Option D
WHERE email = '%@example.com'

Summary

SQL provides string operations for cleaning, transforming, combining, and searching text. Use LOWER(email) or UPPER(text) to normalize capitalization, and TRIM(email) to remove unwanted surrounding whitespace. Functions can be combined, as in LOWER(TRIM(email)). Concatenation creates new text values (first_name || ' ' || last_name), SUBSTRING(referral_code FROM 1 FOR 3) extracts part of a value, REPLACE(referral_code, '-', '/') changes known text, and LIKE 'A%' handles pattern matching.

Next, we will learn how SQL handles different data types and how to convert values safely using explicit casts and type conversion.