String Functions

Introduction

Real-world data is rarely stored exactly how you want to display it. Names might be split across two columns, text might have extra spaces, or you might need to show only part of a value. MySQL provides a rich set of built-in string functions to handle exactly this kind of cleanup and transformation.

In this lesson we will use a simple "students" table to explore the most commonly used string functions: CONCAT(), UPPER(), LOWER(), LENGTH(), TRIM(), SUBSTRING(), and REPLACE().

CREATE TABLE students (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO students (id, first_name, last_name, email) VALUES
(1, 'Alex', 'Johnson', '  alex.johnson@mail.com'),
(2, 'priya', 'Sharma', 'PRIYA.SHARMA@MAIL.COM'),
(3, 'Sam', 'Lee', 'sam.lee@mail.com  ');

Why String Functions Matter

String functions let you transform text data directly inside a SQL query, without pulling the data into an application first. This is useful for formatting output, cleaning messy imported data, and building derived values like a full name from separate first and last name columns.

CONCAT()

CONCAT() joins two or more strings into one. It is one of the most frequently used string functions, especially for combining columns like first_name and last_name into a single display value.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

+----------------+
| full_name      |
+----------------+
| Alex Johnson   |
| priya Sharma   |
| Sam Lee        |
+----------------+

UPPER() and LOWER()

UPPER() converts text to all uppercase, and LOWER() converts text to all lowercase. These are commonly used to standardize data for comparisons or for consistent display.

SELECT first_name, UPPER(first_name) AS upper_name, LOWER(email) AS lower_email
FROM students;

+------------+------------+---------------------------+
| first_name | upper_name | lower_email               |
+------------+------------+---------------------------+
| Alex       | ALEX       |   alex.johnson@mail.com   |
| priya      | PRIYA      | priya.sharma@mail.com     |
| Sam        | SAM        | sam.lee@mail.com          |
+------------+------------+---------------------------+

LENGTH()

LENGTH() returns the number of bytes in a string (for most simple text, this equals the number of characters). It is useful for validating data or spotting unexpectedly short or long values.

SELECT first_name, LENGTH(first_name) AS name_length
FROM students;

+------------+-------------+
| first_name | name_length |
+------------+-------------+
| Alex       | 4           |
| priya      | 5           |
| Sam        | 3           |
+------------+-------------+

TRIM()

TRIM() removes leading and trailing spaces from a string. This is extremely useful when cleaning up data that was imported from a spreadsheet or entered by a user, where stray spaces are common.

SELECT email, TRIM(email) AS cleaned_email
FROM students
WHERE id = 1;

+---------------------------+-----------------------+
| email                     | cleaned_email          |
+---------------------------+-----------------------+
|   alex.johnson@mail.com   | alex.johnson@mail.com |
+---------------------------+-----------------------+

MySQL also supports LTRIM() (removes leading spaces only) and RTRIM() (removes trailing spaces only), if you only need to trim one side.

SUBSTRING()

SUBSTRING() extracts part of a string, starting at a given position for a given length. Position numbering starts at 1, not 0.

SELECT first_name, SUBSTRING(first_name, 1, 3) AS short_name
FROM students;

+------------+------------+
| first_name | short_name |
+------------+------------+
| Alex       | Ale        |
| priya      | pri        |
| Sam        | Sam        |
+------------+------------+

REPLACE()

REPLACE() finds every occurrence of one substring inside a string and swaps it for another. It is handy for quick corrections, like fixing a wrong domain name across many email addresses.

SELECT email, REPLACE(email, '@mail.com', '@school.edu') AS new_email
FROM students;

+---------------------------+-------------------------------+
| email                     | new_email                     |
+---------------------------+-------------------------------+
|   alex.johnson@mail.com   |   alex.johnson@school.edu     |
| PRIYA.SHARMA@MAIL.COM     | PRIYA.SHARMA@MAIL.COM         |
| sam.lee@mail.com          | sam.lee@school.edu            |
+---------------------------+-------------------------------+

Reference Table

Here is a quick reference for the string functions covered in this lesson.

Worked Example: Full Name

Let's combine several of these functions to build a single, clean, properly formatted full name from the messy first_name and email columns.

SELECT
    CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)), ' ', last_name) AS full_name,
    TRIM(LOWER(email)) AS clean_email
FROM students;

+----------------+------------------------+
| full_name      | clean_email            |
+----------------+------------------------+
| Alex Johnson   | alex.johnson@mail.com  |
| Priya Sharma   | priya.sharma@mail.com  |
| Sam Lee        | sam.lee@mail.com       |
+----------------+------------------------+

This query capitalizes only the first letter of first_name (using SUBSTRING() to split it into its first character and the rest), lowercases the remainder, joins it with last_name using CONCAT(), and separately produces a trimmed, lowercased email — all directly inside SQL.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

String functions let you clean and transform text directly inside your SQL queries, saving you from writing extra application code just to format data.

In this lesson, you learned CONCAT(), UPPER(), LOWER(), LENGTH(), TRIM(), SUBSTRING(), and REPLACE(), and combined several of them to build a clean full name. Next, you will explore MySQL's numeric functions.