CONCAT / LENGTH

CONCAT: Concatenates strings (MySQL, PostgreSQL, SQL Server).

CONCAT(string1, string2, ...)

|| operator: Concatenates strings (PostgreSQL, Oracle, SQLite, SQL standard).

string1 || string2

LENGTH: Returns the byte length or character count of a string.

LENGTH(string)

CHAR_LENGTH / CHARACTER_LENGTH: Returns the number of characters.

CHAR_LENGTH(string)

The following examples use the employees table.

Concatenate last name and first name to create a full name.

sample_concat_length.sql

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

+---------------+--------------------+
| full_name     | email              |
+---------------+--------------------+
| item_a user_a | user_a@example.com |
| item_b user_b | user_b@example.com |
| item_b user_c | user_c@example.com |
+---------------+--------------------+
3 rows in set

Concatenate with an underscore separator.

sample_concat_length.sql

SELECT
    CONCAT_WS('_', last_name, first_name) AS name_code,
    email
FROM employees;

+---------------+--------------------+
| name_code     | email              |
+---------------+--------------------+
| item_a_user_a | user_a@example.com |
| item_b_user_b | user_b@example.com |
| item_b_user_c | user_c@example.com |
+---------------+--------------------+
3 rows in set

Get the character length of email addresses.

sample_concat_length.sql

SELECT
    email,
    CHAR_LENGTH(email) AS email_length
FROM employees
ORDER BY email_length;

+--------------------+--------------+
| email              | email_length |
+--------------------+--------------+
| user_a@example.com |           18 |
| user_b@example.com |           18 |
| user_c@example.com |           18 |
+--------------------+--------------+
3 rows in set

Get the length of the username portion (before @) in an email address.

sample_concat_length.sql

SELECT
    email,
    CHAR_LENGTH(
        SUBSTRING(email, 1, POSITION('@' IN email) - 1)
    ) AS username_length
FROM employees;

+--------------------+-----------------+
| email              | username_length |
+--------------------+-----------------+
| user_a@example.com |               6 |
| user_b@example.com |               6 |
| user_c@example.com |               6 |
+--------------------+-----------------+
3 rows in set

CONCAT works across MySQL, PostgreSQL, and SQL Server. Oracle has supported CONCAT since version 12c, but it is limited to two arguments. To concatenate three or more strings, use the || operator.

-- Oracle: CONCAT accepts only two arguments. Use || for three or more.
SELECT last_name || ' ' || first_name AS full_name FROM employees;

The || operator is available in PostgreSQL, Oracle, and SQLite. SQL Server does not support ||, so use CONCAT or the + operator instead.

-- SQL Server: Use the + operator to concatenate (returns NULL if any value is NULL).
SELECT last_name + ' ' + first_name AS full_name FROM employees;

CHAR_LENGTH works the same in MySQL and PostgreSQL. In Oracle, LENGTH already returns the character count, so it can be used directly. In SQL Server, use LEN.

-- SQL Server: LEN returns the character count (trailing spaces are excluded).
SELECT email, LEN(email) AS email_length FROM employees;

Most DBMSs return NULL from CONCAT if any argument is NULL. To skip NULL values, use CONCAT_WS or convert NULLs to empty strings with COALESCE before passing them.

When counting characters, MySQL's LENGTH returns the byte length, so a Japanese character (UTF-8) is counted as 3 bytes. Use CHAR_LENGTH to accurately count the number of Japanese characters.

For extracting substrings, see SUBSTRING / TRIM. For finding character positions, see POSITION / INSTR.