MySQL String Functions: CONCAT, SUBSTRING, TRIM, REPLACE, and LENGTH

1. CONCAT: Combining Strings in MySQL

The CONCAT function allows you to combine two or more strings into a single string. It’s especially useful for creating full names, URLs, or concatenated messages.

Syntax

CONCAT(string1, string2, ..., stringN)

Example

SELECT CONCAT('Hello', ' ', 'World') AS greeting;

Explanation:

1. 'Hello' and 'World' are the strings we want to combine.
2. ' ' (space) is added between them to make the output readable.
3. The function returns Hello World.

Real-World Example

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

• Combines the first_name and last_name columns into a single full_name column.
• Useful for reports or generating display names.

2. SUBSTRING: Extracting Parts of a String

SUBSTRING (or SUBSTR) allows you to extract a portion of a string based on starting position and length.

Syntax

SUBSTRING(string, start_position, length)

• start_position: The position in the string to begin extraction (1-based index).
• length: The number of characters to extract (optional).

Example

SELECT SUBSTRING('MySQLTutorial', 6, 8) AS sub_text;

Explanation:

1. Start at position 6 (T in Tutorial).
2. Extract 8 characters.
3. Result: Tutorial.

Practical Use Case

SELECT SUBSTRING(email, 1, LOCATE('@', email) - 1) AS username
FROM users;

• Extracts the username part of an email.
• LOCATE('@', email) finds the position of @.
• Subtracting 1 ensures we exclude the @ symbol.

3. TRIM: Removing Unwanted Spaces

The TRIM function removes leading and trailing spaces (or specified characters) from a string.

Syntax

TRIM([LEADING | TRAILING | BOTH] 'character' FROM string)

• By default, it removes spaces from both ends of a string.

Example

SELECT TRIM('   Hello World   ') AS trimmed_text;

Explanation:

• Leading and trailing spaces are removed.
• Output: Hello World.

Advanced Use

SELECT TRIM(BOTH '.' FROM '...example...') AS cleaned_text;

• Removes periods from both ends of the string.
• Result: example.

4. REPLACE: Substituting Text Within a String

REPLACE is used to replace all occurrences of a substring with another substring.

Syntax

REPLACE(string, from_substring, to_substring)

Example

SELECT REPLACE('I love MySQL', 'MySQL', 'SQL') AS replaced_text;

Explanation:

• Finds all occurrences of 'MySQL'.
• Replaces them with 'SQL'.
• Output: I love SQL.

Use Case: Data Cleaning

UPDATE products
SET description = REPLACE(description, 'colour', 'color');

• Standardizes spelling across records in the description column.

5. LENGTH: Measuring String Size

The LENGTH function returns the number of characters in a string, including spaces.

Syntax

LENGTH(string)

Example

SELECT LENGTH('Hello World') AS text_length;

Explanation:

• Counts all characters including spaces.
• Output: 11.

Practical Example

SELECT LENGTH(TRIM(name)) AS name_length
FROM customers;

• Measures the length of customer names after removing unwanted spaces.
• Useful for validating or formatting data.

Combining Functions for Powerful Queries

These string functions can be combined to perform more advanced operations. For example, creating a formatted username from an email:

SELECT CONCAT(UPPER(SUBSTRING(email, 1, 1)), 
              SUBSTRING(TRIM(email), 2, LOCATE('@', email) - 2)) AS username
FROM users;

Explanation:

1. SUBSTRING(email, 1, 1) takes the first character and converts it to uppercase.
2. TRIM(email) removes unwanted spaces.
3. SUBSTRING(..., 2, LOCATE('@', email) - 2) gets the rest of the username.
4. CONCAT merges these parts into a properly formatted username.