SQL CONCAT()

The SQL CONCAT() function is used to join two or more strings into a single string. It is a commonly used function when you want to combine text values from multiple columns or add extra formatting to text data in SQL queries.

The CONCAT() function is available in most SQL databases, including MySQL, SQL Server, and PostgreSQL, though some syntax variations may apply.

In this tutorial, we will go through SQL CONCAT() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL CONCAT() Function

The basic syntax of the SQL CONCAT function is:

</>
Copy
CONCAT(string1, string2, ...);

Each part of this syntax has a specific purpose:

  • string1, string2, …: The strings to concatenate. You can include as many strings as you need, separated by commas.

If any argument is NULL, CONCAT will return NULL as the result unless you use CONCAT_WS in MySQL, which can handle NULL values by skipping them.

Setup for Examples: Creating the Database and Table

We’ll create a sample students table to demonstrate the CONCAT function examples, combining different fields for text formatting and querying.

1. First, create a new database called school:

</>
Copy
CREATE DATABASE school;

2. Select the school database to work with:

</>
Copy
USE school;

3. Create a table named students with columns id, first_name, last_name, and age:

</>
Copy
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

4. Insert sample data into the students table:

</>
Copy
INSERT INTO students (first_name, last_name, age)
VALUES 
('Alice', 'Smith', 14),
('Bob', 'Jones', 15),
('Charlie', NULL, 13),
('David', 'Johnson', NULL);

With this setup complete, we can run the CONCAT function examples to test and view results in the students table.

SQL CONCAT() Function - Setup for Examples

Examples: Using CONCAT in SQL Queries

We’ll go through various examples demonstrating the CONCAT function in SQL, using sample data from a students table with fields first_name, last_name, and age.

SQL CONCAT() Function - Table data for examples - students table

1. Concatenating First Name and Last Name

To combine first_name and last_name into a single column called full_name:

</>
Copy
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

This query returns each student’s full name by combining their first and last names with a space in between.

SQL CONCAT() Function Example - Concatenating First Name and Last Name

2. Adding Extra Text to Concatenated Values

To add descriptive text to the age field, such as “years old” after each student’s age:

</>
Copy
SELECT CONCAT(first_name, ' ', last_name, ' is ', age, ' years old') AS description
FROM students;

This query returns a sentence for each student, such as “Alice Smith is 14 years old.”

SQL CONCAT() Function Example - Adding Extra Text to Concatenated Values

3. Concatenating Multiple Fields with NULL Handling (CONCAT_WS in MySQL)

To combine first_name and last_name even if one of the fields is NULL, use CONCAT_WS (Concatenate with Separator):

</>
Copy
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM students;

Using CONCAT_WS with a space as the separator skips NULL values, so only non-null values are included in full_name.

SQL CONCAT() Function Example - Concatenating Multiple Fields with NULL Handling

Reference: SQL NULL

4. Using CONCAT in a WHERE Clause

To search for students whose full name matches a specific string, use CONCAT within the WHERE clause:

</>
Copy
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students
WHERE CONCAT(first_name, ' ', last_name) = 'Alice Smith';

This query returns records where the full name matches “Alice Smith”.

SQL CONCAT() Function Example - Using CONCAT in a WHERE Clause

Reference: SQL WHERE

FAQs for SQL CONCAT

1. What does the SQL CONCAT function do?

The CONCAT function joins two or more strings together, returning a single concatenated string.

2. What happens if CONCAT encounters a NULL value?

If any value passed to CONCAT is NULL, the entire result is NULL. To handle NULL values, use CONCAT_WS in MySQL, which skips NULL values.

3. How is CONCAT_WS different from CONCAT?

CONCAT_WS (Concatenate with Separator) allows you to specify a separator and handles NULL values by skipping them, unlike CONCAT, which returns NULL if any argument is NULL.

4. Can CONCAT be used with numeric data?

Yes, CONCAT can combine numeric and string data. Numeric values are automatically converted to strings during concatenation.

5. Is CONCAT supported in all SQL databases?

Yes, CONCAT is widely supported in SQL databases, including MySQL, SQL Server, PostgreSQL, and Oracle.