VMware ESXi and vSphere Cluster Management

MySQL String Functions: CONCAT, LENGTH, CHAR_LENGTH, and REPLACE

Learn how MySQL string functions transform and inspect text with CONCAT, LENGTH, CHAR_LENGTH, and REPLACE using practical SELECT examples.

A string is a sequence of text characters stored as a value. MySQL string functions read, inspect, combine, or transform those values in SQL queries. A function can work with literal text, a column, or a larger expression.

This lesson focuses on calculated results returned by SELECT. These examples do not change the data stored in a table.

Example data: the testtb table

Use this small contacts-style table. The name and surname columns contain text, while year is an integer included as an additional sample field.

CREATE TABLE testtb (
  name VARCHAR(50),
  surname VARCHAR(100),
  year INT
);

INSERT INTO testtb (name, surname, year) VALUES
  ('Amy', 'Bryant', 1991),
  ('Mark', 'Smith', 1955),
  ('John', 'von Neumann', 1921),
  ('Aaron', 'Rogers', 1995),
  ('Brian', 'Cormier', 1988);

Inspect the source rows with:

SELECT * FROM testtb;

A column is a named field that supplies a value for each row. A literal is a fixed value written directly in a statement, such as ' ' or 'website'. String literals are quoted; column names are not.

CONCAT: join strings together

CONCAT combines two or more string arguments into one result. Its general syntax is:

CONCAT(string1, string2, ...)

To create a full name from two columns, include a quoted space between them:

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

The expression uses the name and surname columns, plus the literal space ' '. The result has one calculated full_name value for each source row, such as Amy Bryant and John von Neumann.

Without the separator, the components would run together:

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

That query could return AmyBryant, so pass every desired separator as an additional argument.

LENGTH: measure bytes

LENGTH returns the size of a string in bytes. A byte is a unit of storage. The number of bytes is not always the same as the number of visible characters.

SELECT LENGTH('Amy') AS byte_length;

A character set defines how text characters are represented as bytes. In a multibyte character set, one character can require more than one byte. Therefore, LENGTH can return a larger number than the visible character count.

For example, compare a string containing a non-ASCII character:

SELECT
  LENGTH('café') AS byte_length,
  CHAR_LENGTH('café') AS character_length;

With a multibyte character set, the byte length may be greater than the character length. Use LENGTH when storage size in bytes matters, such as when considering encoding or byte-based limits.

CHAR_LENGTH: count characters

CHAR_LENGTH counts characters rather than bytes. It is the character-oriented counterpart to LENGTH.

SELECT CHAR_LENGTH(name) AS name_length
FROM testtb;

The result contains one row for each row in testtb. For the sample names, the expected counts include:

  • Amy: 3 characters
  • Mark: 4 characters
  • John: 4 characters
  • Aaron: 5 characters
  • Brian: 5 characters

For user-visible text validation, CHAR_LENGTH is generally preferable because users think in characters, not encoded storage bytes. For example, a rule such as “the display name must be no longer than 30 characters” normally calls for CHAR_LENGTH.

You can compare both measurements for every name:

SELECT
  name,
  LENGTH(name) AS byte_length,
  CHAR_LENGTH(name) AS character_length
FROM testtb;

REPLACE: substitute matching text

REPLACE returns a string in which every matching occurrence of one substring is exchanged for replacement text. Its three arguments are ordered as follows:

REPLACE(source_string, text_to_find, replacement_text)

For example, correct a misspelled word in a literal sentence:

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

The returned value is About this website. The first argument is the source string, the second is the exact text to find, and the third is the text to insert in its place.

REPLACE can also operate on a column. For example, this produces a transformed display value for each row:

SELECT
  surname,
  REPLACE(surname, 'von', 'Von') AS displayed_surname
FROM testtb;

Using REPLACE inside SELECT does not update the stored column. It only calculates a value for the result set. Persistent corrections require a deliberately written data-changing statement, such as an appropriate UPDATE.

Putting functions in the SELECT list

Functions can appear directly in the SELECT list alongside ordinary columns. A result-column alias gives a calculated value a readable name:

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

Because the query reads from testtb, MySQL returns one result row for each source row. The query calculates a full name and a character count without changing name or surname.

Troubleshooting string-function queries

A length is larger than the visible character count

This usually means LENGTH is reporting bytes and the active character set uses multiple bytes for one or more characters. Use CHAR_LENGTH when the requirement is a character count.

Combined names run together

If the result looks like AmyBryant, the query omitted a separator. Add a quoted space as an argument:

CONCAT(name, ' ', surname)

The replacement appears in the query result, but the table is unchanged

REPLACE in a SELECT expression transforms only the returned value. It does not write the transformed value back to the table.

REPLACE does not find the expected text

Check that the search substring matches the stored text in spelling, spacing, and punctuation. Case behavior can also depend on the column or expression's collation. Inspect the original value and use an exact search argument appropriate to that collation.

Exam-relevant notes

  • CONCAT(name, ' ', surname) combines columns and a literal separator.
  • LENGTH returns a byte count.
  • CHAR_LENGTH returns a character count.
  • CHAR_LENGTH is usually the right choice for user-visible length validation.
  • REPLACE(source, find, replacement) substitutes matching substrings in the calculated result.
  • Functions in a SELECT list calculate output; they do not change stored rows by themselves.