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.