MySQL online course

MySQL String Functions

Learn how MySQL string functions transform, combine, measure, and replace text using CONCAT, LENGTH, CHAR_LENGTH, and REPLACE.

MySQL string functions work with textual data. A string is a sequence of text characters, either stored in a character column or written directly as a string literal. String functions can transform, inspect, combine, or replace text in a query.

Functions can accept string literals, column values, or expressions. Their results can appear in SELECT output without changing the values stored in a table.

This lesson assumes that you understand basic SELECT statements, tables, columns, quoted SQL values, and the meaning of NULL. For background, see MySQL Functions, Query a Database, and MySQL Data Types.

Sample contacts data

Use this small contacts-style table for the examples. The surname column includes a multiword value to show that functions operate on the complete text stored in a value.

To inspect the source rows before applying a function, run:

SELECT name, surname, year
FROM testtb;

CONCAT: combine strings

CONCAT joins two or more strings into one result.

CONCAT(string1, string2, ...)

Column names are written without quotes. Fixed text values, called string literals, are enclosed in quotes. The following query inserts a space literal between the two name columns and assigns the calculated result the column alias full_name.

SELECT
    name,
    surname,
    CONCAT(name, ' ', surname) AS full_name
FROM testtb;

The value for Maria becomes Maria De la Cruz; the multiword surname remains one complete text value. AS full_name creates a temporary output name for the expression. It does not add a new column to the table. See Aliases for more about naming calculated output.

NULL behavior with CONCAT

NULL represents a missing or unknown value. It is different from an empty string. If any argument passed to CONCAT is NULL, the result is NULL.

SELECT CONCAT('Ada', ' ', NULL) AS full_name;

If missing values should not discard the entire result, supply fallback values with COALESCE, or use CONCAT_WS (“concatenate with separator”). CONCAT_WS can skip NULL values while placing a separator between non-NULL arguments.

SELECT CONCAT_WS(' ', name, surname) AS full_name
FROM testtb;

For columns that might contain empty strings or extra whitespace, you may also need TRIM and explicit handling for empty values.

LENGTH and CHAR_LENGTH

LENGTH and CHAR_LENGTH measure different properties:

• LENGTH(str) returns the size of a string in bytes.
• CHAR_LENGTH(str) returns the number of characters in a string.

Use CHAR_LENGTH when you need a human-visible character count, such as the length of a person's name.

SELECT
    name,
    CHAR_LENGTH(name) AS name_characters
FROM testtb;

A multibyte character set, such as utf8mb4, can represent some characters with more than one byte. Therefore, byte length and character count may differ.

SELECT
    LENGTH('é') AS bytes,
    CHAR_LENGTH('é') AS characters;

With a character set in which é uses two bytes, this returns 2 for bytes and 1 for characters. The exact byte count depends on the character set and the characters in the value.

REPLACE: substitute text

REPLACE substitutes every occurrence of a target substring with a replacement substring.

REPLACE(str, from_str, to_str)

For example, this corrects a misspelled word in a string literal:

SELECT REPLACE(
    'About this webseite',
    'webseite',
    'website'
) AS corrected_text;

The function also accepts a column expression. This query shows the original value and a proposed replacement side by side:

SELECT
    description,
    REPLACE(description, 'webseite', 'website') AS corrected_description
FROM pages;

If no matching substring exists, REPLACE returns the input unchanged. It replaces every matching occurrence, not only the first one, so preview representative rows before using a broad replacement.

Display transformation versus stored data

Using REPLACE inside SELECT only computes transformed output for that result set. It does not change the source column. If the correction must be stored, first test the expression with SELECT, then use a carefully scoped UPDATE.

UPDATE pages
SET description = REPLACE(description, 'webseite', 'website')
WHERE description LIKE '%webseite%';

An UPDATE modifies stored data, so check the WHERE condition and back up or test data according to your workflow. Learn more about updating the contents of a field.

Case and collation

A collation is a set of rules MySQL uses to compare and sort character data. Matching behavior for text operations can depend on the collation of the expression or column. If text with different letter case does not match as expected, inspect the collation and explicitly control collation or normalize case when case-specific behavior is required.

Core string function reference

Using string functions safely in queries

String functions are expressions, so they can appear in a SELECT list alongside ordinary columns. Use column aliases to make calculated results understandable.

SELECT
    CONCAT(name, ' ', surname) AS full_name,
    CHAR_LENGTH(name) AS name_length,
    year
FROM testtb;

Keep the distinction between a read-only query and a data-modification statement in mind:

• SELECT CONCAT(...), SELECT CHAR_LENGTH(...), and SELECT REPLACE(...) calculate values for display.
• UPDATE changes values stored in rows.

A reliable workflow is to write a SELECT that displays the original and transformed values, inspect the result, and only then create a narrowly targeted UPDATE if persistence is required.

Troubleshooting

• CONCAT returns NULL: At least one argument is NULL. Check the source columns, use COALESCE for fallback text, or use CONCAT_WS for separator-aware joining.
• Names have missing pieces or extra spaces: A column may contain NULL, an empty string, or leading and trailing whitespace. Use appropriate COALESCE, CONCAT_WS, and TRIM expressions.
• LENGTH and CHAR_LENGTH differ: The text includes characters represented by multiple bytes. Choose CHAR_LENGTH for character counts and LENGTH for byte size.
• REPLACE does not change the table: It was used in a SELECT, which only displays a computed value. Use a reviewed UPDATE to persist a correction.
• More occurrences changed than expected: REPLACE substitutes every match. Preview results and use more specific matching text when necessary.
• Different letter case does not match: The expression's collation may affect matching. Inspect or explicitly set the collation, or normalize case before comparison.

Key points

• String functions process literals, columns, and expressions without necessarily modifying stored data.
• CONCAT joins strings, but returns NULL when any argument is NULL.
• LENGTH counts bytes; CHAR_LENGTH counts characters.
• REPLACE changes every matching occurrence in its returned expression and leaves no-match input unchanged.
• Use column aliases such as full_name and name_length to label calculated output.
• Test transformations with SELECT before using them in UPDATE.