🚀 SQL Roadmap 2026 — Part 9

SQL String Functions — Cleaning & Transforming Text Data

In real-world databases, a huge amount of information is stored as text:

• Customer names

• Email addresses

• Phone numbers

• Product names

• Cities

• Categories

• Addresses

• Job titles

But text data is rarely perfectly clean.

You may encounter:

• ' Alice '

• 'alice@example.com'

• 'ALICE@EXAMPLE.COM'

• 'Premium Customer'

• ' Mumbai'

SQL string functions allow you to clean, search, extract, combine, and transform text directly inside your queries.

🧠 1. What Are String Functions?

String functions are SQL functions that operate on text values.

Common functions include:

• LENGTH()

• UPPER()

• LOWER()

• TRIM()

• LTRIM()

• RTRIM()

• SUBSTRING()

• LEFT()

• RIGHT()

• CONCAT()

• REPLACE()

• POSITION()

• CHAR_LENGTH()

Exact function names and syntax can vary slightly between databases such as PostgreSQL, MySQL, SQL Server, and Oracle.

🔠 2. UPPER()

Converts text to uppercase.

SELECT
    customer_name,
    UPPER(customer_name) AS uppercase_name
FROM customers;

Example:

Alice becomes ALICE

Useful for:

• Standardizing text

• Case-insensitive comparisons

• Creating reports

• Data cleaning

🔡 3. LOWER()

Converts text to lowercase.

SELECT
    LOWER(email) AS email
FROM customers;

Example:

ALICE@EXAMPLE.COM becomes alice@example.com

A common data-cleaning pattern is:

SELECT
    LOWER(TRIM(email)) AS cleaned_email
FROM customers;

This handles both unnecessary spaces and inconsistent capitalization.

🧹 4. TRIM()

Removes leading and trailing spaces.

SELECT
    TRIM(customer_name) AS cleaned_name
FROM customers;

For example ' Alice ' becomes 'Alice'

This is extremely useful when importing data from Excel, CSV files, APIs, and external systems.

↩️ 5. LTRIM() and RTRIM()

• LTRIM() removes spaces from the beginning:

  SELECT LTRIM(customer_name) FROM customers;
  

• RTRIM() removes spaces from the end:

  SELECT RTRIM(customer_name) FROM customers;
  

• While TRIM() generally handles both sides:

  SELECT TRIM(customer_name) FROM customers;
  

📏 6. LENGTH()

Returns the number of characters in a string.

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

Example:

• Alice → 5

• Robert → 6

Function behavior can vary across SQL dialects, particularly with multibyte characters.

🔍 7. Finding Long or Short Values

String length can be useful for data-quality checks.

Example:

SELECT * FROM customers WHERE LENGTH(phone) < 10;

This can help identify potentially invalid phone numbers.

SELECT * FROM products WHERE LENGTH(product_name) > 100;

This can identify unusually long product descriptions.

✂️ 8. SUBSTRING()

SUBSTRING() extracts part of a string.

A common form is: SUBSTRING(column_name, start_position, length)

SELECT SUBSTRING(customer_name, 1, 3) AS first_three_characters FROM customers;

For Alexander the result would be Ale.

Syntax differs by database, so always check the dialect you're using.

👈 9. LEFT()

Returns characters from the beginning of a string.