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)

-- Concatenate first name and last name to create a full name.
SELECT
    CONCAT(last_name, ' ', first_name) AS full_name,
    email
FROM employees;

-- Concatenate address parts with a separator.
SELECT
    CONCAT_WS(' ', prefecture, city, street) AS address
FROM customers;

-- Filter products by the character length of the product name.
SELECT product_name, CHAR_LENGTH(product_name) AS name_length
FROM products
WHERE CHAR_LENGTH(product_name) <= 10
ORDER BY name_length;

-- Get the length of the username portion (before @) in an email address.
SELECT
    email,
    CHAR_LENGTH(
        SUBSTRING(email, 1, POSITION('@' IN email) - 1)
    ) AS username_length
FROM employees;

-- Full name concatenation result.
full_name      | email
---------------+-------------------------
Taro Yamada    | yamada@example.com
Hanako Suzuki  | suzuki@example.com
Jiro Sato      | sato@example.com

-- Product name character count list.
product_name  | name_length
--------------+------------
Mug           | 3
Ballpoint pen | 13
Notepad       | 7

'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 product_name, LEN(product_name) AS name_length FROM products;

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'.