MySQL CONCAT() Function

  1. Overview
  2. MYSQL
  3. String Functions
  4. MySQL CONCAT() Function

The CONCAT() function in MySQL is used to concatenate (combine) two or more strings or expressions into a single string. It is a simple yet powerful function for handling string concatenation in SQL queries.

Syntax

CONCAT(expression1, expression2, expression3, ...)

Parameters

Parameter Description
expression1, ... Required. The expressions to concatenate. Any number of expressions can be provided.

Definition and Use

  • Purpose: Combines multiple strings into one continuous string.
  • Behavior: If any of the provided expressions is NULL, the function returns NULL. Use the CONCAT_WS() function to handle NULL values gracefully.
  • Special Note: Adds strings without any separator unless explicitly provided as part of the expressions.

Usage Example 1: Simple Concatenation

To combine several strings into one:

SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!") AS ConcatenatedString;

Output:

ConcatenatedString
SQL Tutorial is fun!

Usage Example 2: Concatenating Column Values

To combine data from multiple columns into one "Address" column:

SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address
FROM Customers;

Output Explanation:

  • The function combines values from the Address, PostalCode, and City columns, adding a space between each value.

Technical Details

  • Availability: Available from MySQL 4.0 onwards.
  • Return Type: Returns a single string that combines the input expressions.

Applications

  1. Formatting Data: Create user-friendly outputs by combining column values.
  2. Dynamic Queries: Build dynamic SQL statements or strings within queries.
  3. Custom Labels: Generate labels or descriptive text directly in query results.

Example Outputs

Suppose the Customers table contains the following data:

Address PostalCode City
123 Main St 12345 New York
456 Elm St 67890 Chicago

After running the query:

SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address
FROM Customers;

The result will be:

Address
123 Main St 12345 New York
456 Elm St 67890 Chicago

Key Notes

  • If handling NULL values is crucial, consider using the CONCAT_WS() function. This function allows specifying a separator and ignores NULL values during concatenation.
  • The CONCAT() function does not add any separator between expressions by default. You must explicitly include separators like spaces, commas, or other characters.

By using the CONCAT() function, you can efficiently combine text data in MySQL queries, enabling the creation of dynamic and readable outputs.

Was this article helpful?