SQL String Functions: CONCAT, SUBSTRING, TRIM, REPLACE, and More

Raw text data is rarely stored in exactly the shape you need to display or compare it — names need combining, codes need padding, descriptions need trimming. MySQL's string functions handle all of this directly inside a query, without requiring application code to reformat anything after the fact. This lesson covers the dozen most commonly used string functions you'll reach for constantly.

What Are String Functions?

String functions are built-in MySQL functions that operate on text (CHAR, VARCHAR, TEXT) values, letting you combine, measure, reformat, search within, or transform text directly inside a SQL query. They can be used in SELECT lists, WHERE clauses, ORDER BY clauses, and anywhere else an expression is allowed.

What You'll Learn

• Combine and measure text using CONCAT and LENGTH.
• Change case and trim whitespace using UPPER, LOWER, TRIM, LTRIM, and RTRIM.
• Extract, search, and replace substrings using SUBSTRING, INSTR, and REPLACE.
• Pad and reverse text using LPAD, RPAD, and REVERSE.

Key Terms to Know

• CONCAT: Combines two or more strings into one.
• SUBSTRING: Extracts a portion of a string starting at a given position.
• TRIM: Removes leading and/or trailing whitespace (or specified characters) from a string.
• INSTR: Returns the position of the first occurrence of a substring within a string.

Combining and Measuring Text: CONCAT and LENGTH

CONCAT(first_name, ' ', last_name) joins multiple values into a single string, commonly used to build a full display name from separate first_name and last_name columns without needing two separate columns in the output. CONCAT accepts any number of arguments and automatically converts non-string values like numbers to text.

LENGTH(description) returns the number of bytes in a string (CHAR_LENGTH returns the number of characters, which matters when multi-byte characters like emoji or non-Latin scripts are involved). LENGTH is frequently used in WHERE or CHECK-style validation to flag unusually short or long text values.

Changing Case and Trimming Whitespace

UPPER(email) and LOWER(email) convert text to all-uppercase or all-lowercase respectively, commonly used to normalize values before comparison, such as WHERE LOWER(email) = LOWER('User@Example.com') to make an equality check effectively case-insensitive regardless of the column's collation.

TRIM(name) removes leading and trailing whitespace from a string — extremely common for cleaning up user-submitted form data where accidental extra spaces are common. LTRIM and RTRIM remove whitespace from only the left or only the right side respectively, useful when only one side needs cleaning.

Extracting, Searching, Replacing, Padding, and Reversing

SUBSTRING(product_code, 1, 3) extracts 3 characters starting at position 1 (MySQL string positions are 1-indexed, not 0-indexed), useful for pulling a fixed-position prefix out of a structured code. INSTR(description, 'sale') returns the position where 'sale' first appears within description, or 0 if it isn't found at all, useful for quick existence checks combined with conditional logic.

REPLACE(phone, '-', '') strips out all hyphens from a phone number string, commonly used to normalize differently-formatted input into one consistent format. LPAD(invoice_id, 6, '0') and RPAD pad a string to a fixed length with a specified character on the left or right respectively, often used to zero-pad numeric IDs into fixed-width display codes like 'INV-000042'. REVERSE(text) simply reverses a string's character order, occasionally useful for specific search or validation tricks like palindrome checks.

Visual Summary

Picture a stack of raw, slightly messy index cards: some have stray leading spaces, some have inconsistent capitalization, some have a long product description that needs only its first few characters shown. String functions are a row of specialized tools on a workbench — one tool joins two cards together (CONCAT), one trims the ragged edges (TRIM), one stamps everything to uppercase (UPPER), and one cuts out exactly the section you need (SUBSTRING) — all applied directly as the cards pass through, before they ever reach the final display.

Common String Functions Reference

Function	Purpose	Example
CONCAT(a, b)	Joins strings together	CONCAT(first_name, ' ', last_name)
LENGTH(str)	Returns length in bytes	LENGTH(description)
UPPER(str) / LOWER(str)	Converts case	UPPER(category)
TRIM(str)	Removes leading/trailing whitespace	TRIM(' Aarav ')
SUBSTRING(str, pos, len)	Extracts part of a string	SUBSTRING(code, 1, 3)
REPLACE(str, from, to)	Replaces occurrences of a substring	REPLACE(phone, '-', '')
LPAD(str, len, pad) / RPAD	Pads a string to a fixed length	LPAD(id, 6, '0')

SQL Example

-- Building a clean full display name
SELECT CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2)), ' ', last_name) AS display_name
FROM customers;

-- Normalizing phone numbers by removing dashes and spaces
SELECT phone, REPLACE(REPLACE(phone, '-', ''), ' ', '') AS normalized_phone
FROM customers;

-- Zero-padding invoice numbers into a fixed-width code
SELECT invoice_id, CONCAT('INV-', LPAD(invoice_id, 6, '0')) AS invoice_code
FROM invoices;

-- Case-insensitive email lookup using LOWER
SELECT customer_id, email FROM customers WHERE LOWER(email) = LOWER('User@Example.com');

The first query builds a properly capitalized full name by combining SUBSTRING, UPPER, and LOWER together. The second strips both dashes and spaces from phone numbers using nested REPLACE calls. The third generates a consistent, zero-padded invoice code with LPAD and CONCAT. The fourth demonstrates a practical case-insensitive lookup pattern using LOWER on both sides of the comparison.

Real-World Examples

• CRM systems use CONCAT to build full display names and TRIM to clean up imported contact data from spreadsheets.
• E-commerce platforms use LPAD to generate consistent, zero-padded order or invoice numbers for customer-facing display.
• Customer support tools use REPLACE to normalize phone numbers into a single consistent format before storing or searching them.
• Search features use LOWER or UPPER on both sides of a comparison to implement reliable case-insensitive matching regardless of collation settings.

Best Practices and Pro Tips

• Use CHAR_LENGTH instead of LENGTH when working with multi-byte character sets like utf8mb4, since LENGTH counts bytes, not characters, and can give misleading results for emoji or non-Latin text.
• Apply LOWER or UPPER consistently on both sides of a comparison when you need guaranteed case-insensitive matching, rather than relying solely on the column's collation.
• Chain string functions together (like nested REPLACE calls) for multi-step cleanup, but keep an eye on readability — for genuinely complex transformations, consider whether the cleanup belongs in application code instead.

Common Mistakes to Avoid

• Forgetting that SUBSTRING positions in MySQL are 1-indexed, not 0-indexed, leading to off-by-one extraction errors.
• Using LENGTH when CHAR_LENGTH was actually intended, producing incorrect results on multi-byte text.
• Applying string functions to every row of a huge table inside a WHERE clause in a way that prevents MySQL from using an index efficiently, since the function must be evaluated for every row before filtering.
• Forgetting that REPLACE only replaces exact substring matches — it has no wildcard or pattern support, unlike LIKE.

Interview Questions

Q1. How would you combine a first_name and last_name column into one full name in a SELECT query?

Using CONCAT(first_name, ' ', last_name), which joins the two columns together with a space in between, producing a single combined string in the result set.

Q2. What is the difference between LENGTH and CHAR_LENGTH in MySQL?

LENGTH returns the number of bytes a string occupies, while CHAR_LENGTH returns the number of characters. For multi-byte character sets like utf8mb4, these can differ significantly, especially with emoji or non-Latin scripts.

Q3. How would you extract the first 3 characters of a product code column?

Using SUBSTRING(product_code, 1, 3), which starts at position 1 (MySQL strings are 1-indexed) and extracts exactly 3 characters from that point.

Q4. What does INSTR return if the searched substring isn't found at all?

INSTR returns 0 if the substring doesn't appear anywhere within the string being searched, which can be used directly in conditional logic to check for the absence of a substring.

Practice MCQs

1. Which function joins multiple strings into one?

1. LENGTH
2. CONCAT
3. TRIM
4. REVERSE

Answer: B. CONCAT

Explanation: CONCAT combines two or more string arguments into a single resulting string.

2. What does SUBSTRING(name, 1, 3) return for a string starting at position 1?

1. The last 3 characters
2. The first 3 characters
3. The entire string reversed
4. An error, since positions start at 0

Answer: B. The first 3 characters

Explanation: Starting at position 1 (MySQL's 1-indexed convention) and extracting a length of 3 returns the first 3 characters of the string.

3. Which function would you use to remove leading and trailing spaces from a string?

1. REPLACE
2. INSTR
3. TRIM
4. LPAD

Answer: C. TRIM

Explanation: TRIM removes leading and trailing whitespace (or specified characters) from a string by default.

Quick Revision Points

• CONCAT joins strings; LENGTH/CHAR_LENGTH measure them (bytes vs characters).
• SUBSTRING extracts a portion of a string using 1-indexed positions.
• TRIM/LTRIM/RTRIM remove whitespace; REPLACE substitutes exact substring matches.
• LPAD/RPAD pad strings to a fixed length; REVERSE flips character order.

Conclusion

• String functions let MySQL handle text formatting and cleanup directly inside queries.
• 1-indexed positions and byte-vs-character length are the two most common sources of subtle bugs.
• Combining several string functions together is a common, powerful pattern for building clean, display-ready output.

MySQL's string functions — CONCAT, LENGTH, UPPER/LOWER, TRIM/LTRIM/RTRIM, SUBSTRING, REPLACE, INSTR, LPAD/RPAD, and REVERSE — together cover the overwhelming majority of everyday text manipulation needs directly inside SQL queries, from building display names to normalizing inconsistent input. Understanding 1-indexed positions and the byte-vs-character distinction in LENGTH avoids the most common mistakes. The next lesson covers numeric functions, applying the same kind of direct, in-query transformation to numbers instead of text.