MySQL String Functions

String Functions in Reports

String functions combine, normalize, measure and extract text for query output. They should not replace proper validation or a clean data model.

SELECT full_name,
       CONCAT(full_name, ' - ', class_name) AS student_label
FROM students
ORDER BY student_id;

CONCAT, CONCAT_WS and NULL

SELECT CONCAT('Coding', 'Easily') AS joined,
       CONCAT_WS(' - ', 'Aarav', 'X-A') AS label;

CONCAT returns NULL if any argument is NULL. CONCAT_WS uses a separator and skips NULL arguments after the separator. Use COALESCE when a visible fallback is required.

Case and Whitespace

SELECT UPPER('Meera') AS upper_name,
       LOWER('SQL Tutorial') AS lower_text,
       TRIM('  SQL  ') AS clean_text,
       LTRIM('  left') AS left_clean,
       RTRIM('right  ') AS right_clean;

These transform output. Cleaning stored data requires a reviewed UPDATE and constraints at input. Case conversion depends on character set and collation rules.

CHAR_LENGTH vs LENGTH

SELECT CHAR_LENGTH('Coding Easily') AS characters,
       LENGTH('Coding Easily') AS bytes;

ASCII uses one byte per character here, so results match. Unicode characters can use multiple bytes; use CHAR_LENGTH for user-visible character count and LENGTH for storage bytes.

SUBSTRING, REPLACE and LOCATE

SELECT SUBSTRING('CodingEasily', 1, 6) AS first_word,
       REPLACE('X-A', 'X-', 'Class ') AS class_label,
       LOCATE('Easily', 'CodingEasily') AS start_position;

SUBSTRING positions start at 1. REPLACE is case-sensitive for matching. LOCATE returns 0 when the substring is absent.

Collation, Search and Indexing

Equality, ordering and LIKE behavior depend on collation. Avoid applying LOWER or TRIM to every row in a large search merely to force matching. Normalize at input, choose the correct collation and measure with EXPLAIN. If a transformed search is essential, evaluate a generated/functional indexed expression supported by your design.

Practice

1. Create a student label with class.
2. Display names in uppercase without modifying storage.
3. Compare CHAR_LENGTH and LENGTH on Hindi text.
4. Extract the first three characters of each name.
5. Find the position of “Easily” in CodingEasily.

Quick Summary

• CONCAT combines; case and trim functions normalize output.
• CHAR_LENGTH counts characters and LENGTH bytes.
• SUBSTRING extracts, REPLACE substitutes and LOCATE finds.
• NULL, Unicode, collation and index use must be considered.

Official References

• String Functions and Operators — MySQL 8.4
• Character Sets and Collations
• Functional Key Parts

References reviewed 14 August 2026.