Not long ago, while working on a user data migration project, I realized we often underestimated the importance of MySQL string functions. At first glance, they seem like basic SQL functions, but their importance becomes undeniable once you start cleaning production data, generating usernames, fixing inconsistencies, or debugging strange import results at 2 AM.

For us, the project sounded simple: migrating customer records from three legacy systems to a new platform.

But the reality was a mess.

Some names contained random spaces, email addresses were incorrectly formatted, product descriptions mixed different spellings (e.g., “colour” and “color”), and several reports failed to function correctly because the string length exceeded the frontend limit. Initially, I tried to handle all the problems in the application code, but performance quickly became unreliable once the dataset exceeded several million rows.

Ultimately, we moved most of the cleanup work directly into MySQL queries. These functions became tools we used almost daily.

1. CONCAT: Combining Strings in MySQL

CONCAT is one of those functions that looks trivial until you start generating display fields, export formats, or API-ready values directly from SQL.

The syntax is straightforward:

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

Simple example:

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

Output:

Hello World

Nothing fancy there.

But in our employee reporting module, we used it heavily because the frontend team wanted a ready-to-display full_name field instead of stitching names together in the application layer.

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

Initially, I genuinely felt that doing this in SQL was completely unnecessary. However, after analyzing several reporting APIs, we discovered that if the formatting was already done in the query results, the response serialization was more concise and the speed was slightly improved.

The improvement wasn’t significant, perhaps only a few milliseconds faster per request, but when these reports were generated thousands of times a day, the cumulative effect was considerable.

Later, we encountered an issue: the CONCAT function would return NULL if any parameter was NULL.

This led to an annoying bug: some users’ display names would suddenly become blank because last_name in the old records was empty. Ultimately, we solved this problem by using the IFNULL() function to handle nullable columns.

I admit, we discovered this problem much later than I expected.

2. SUBSTRING: Extracting Parts of a String

SUBSTRING became extremely useful during email normalization and log analysis.

Syntax:

SUBSTRING(string, start_position, length)

• start_position starts at 1, not 0
• length is optional

Example:

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

Result:

Tutorial

Pretty standard.

The real problem it helps us solve is that, during the account migration process, the username can be extracted from the email address.

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

This worked surprisingly well… until we found corrupted records.

Some imported rows literally had values like:

john.smithgmail.com

No @.

That caused LOCATE() to return 0, and the substring logic started behaving weirdly. I remember staring at several empty username results before realizing the source data itself was broken.

We finally added validation before extraction.

Honestly, this was one of those moments where SQL logic looked correct, but production data reminded us that users can destroy every assumption.

3. TRIM: Removing Unwanted Spaces

I used to ignore TRIM completely.

Then we imported CSV files from a vendor system.

Huge mistake.

Some rows had trailing spaces, others had invisible leading spaces, and a few fields somehow had tabs mixed into the values. At one point we had duplicate customer accounts because:

Acme

and

Acme 

were treated differently.

That bug alone wasted half a day.

Basic syntax:

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

Most of the time we just used:

SELECT TRIM('   Hello World   ') AS trimmed_text;

Result:

Hello World

We later started using custom characters to clean up imported identifiers.

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

Output:

example

To my surprise, adding TRIM() during the temporary table processing made the join operations so clean and efficient.

Before the cleanup, some join operations would silently fail because the values ​​looked exactly the same but actually contained extra spaces. These errors were particularly tricky because everything looked correct before the raw bytes were checked.

4. REPLACE: Substituting Text Within a String

REPLACE became our emergency cleanup tool.

Syntax:

REPLACE(string, from_substring, to_substring)

Simple example:

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

Output:

I love SQL

In production, we mainly used it for data standardization.

Example:

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

Interestingly, it all started with inconsistent search filtering.

Users searching for “color” couldn’t find products imported using British spelling. Initially, we tried fixing this in Elasticsearch, then tried middleware normalization, and finally someone said:

“Why don’t we just clean the source data?”

Which, in hindsight, was obviously the right move.

We also used REPLACE() during URL migrations when old CDN domains changed. Running bulk updates directly in SQL ended up being much faster than writing one-off scripts.

One warning though: on large tables, careless UPDATE + REPLACE queries can lock rows for a while. We learned that the hard way on a table with around 8 million records.

CPU spiked, replication lag appeared, Slack notifications exploded.

After that, we switched to batch updates with limits.

5. LENGTH: Measuring String Size

LENGTH looks boring until frontend validation and database constraints start disagreeing.

Syntax:

LENGTH(string)

Example:

SELECT LENGTH('Hello World') AS text_length;

Output:

11

Pretty simple.

We used this mostly during import validation.

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

The TRIM() function proved more useful than expected because users frequently add spaces accidentally.

During debugging, I also encountered a strange issue: some records appeared to be long enough, but actually exceeded the field limit. It turned out that hidden Unicode characters in the copied Excel data were adding bytes.

It took me a long time to figure this out.

Also worth remembering:

• LENGTH() counts bytes
• CHAR_LENGTH() counts actual characters

This distinction becomes especially important when multilingual data enters the system.

We encountered this problem when processing Japanese customer names because the number of characters and bytes in Japanese names varies significantly.

Combining Functions for Real-World Queries

In practice, these functions are rarely used alone.

One query we used during account initialization looked like this:

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

This feature includes:

• Extracting the first letter of an email address
• Converting it to uppercase
• Removing extra spaces
• Extracting the remaining username portion
• Merging all content into a formatted username

Example:

[email protected]

becomes:

John.doe

This isn’t some groundbreaking logic, but it’s incredibly practical.

Frankly, this is how most SQL work in real-world systems. It’s usually not about writing clever queries, but about dealing with inconsistent data, handling extreme cases where no records are kept, and gradually improving messy datasets without breaking the production environment.

The longer I work with databases, the more I realize that string functions are less a syntax issue and more crucial for crisis management.