SQL Concatenation
In this lesson, you will learn how to concatenate data (strings, numbers, date etc..) as a one string using SQL.
Concatenate Data In SQL
Concatenation means append one string to the end of another string.
It is very important to give a meaningful name for the column returned as a result for the concatenating columns because the database server will not generate a good name.
The way for applying concatenation varies from one database to another.
- In MySQL, we use the
CONCAT()function. - In Oracle and SQLite, we just add a
||between columns. - In SQL Server, we can use the
CONCAT()function or just add a+between columns.
In this tutorial, we will apply concatenation using MySQL concat function.
Preparing A Database
Since you will learn how to give an alias name for a column, let's create a database table that aliases names are useful to apply on them. Execute the following query to create the database.
Example
-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;
-- Create a new database called "demo"
CREATE DATABASE demo;
-- Select the "demo" database
USE demo;
-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
salary DECIMAL(7,2)
);
-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "Johnson", 800);
INSERT INTO employees VALUES (null, "Benjamin", "Anderson", 750);
INSERT INTO employees VALUES (null, "Olivia", "Smith", 900);
INSERT INTO employees VALUES (null, "William", "Davis", 845);
INSERT INTO employees VALUES (null, "Sophia", "Brown", 1000);
INSERT INTO employees VALUES (null, "Daniel", "Martinez", 700);
INSERT INTO employees VALUES (null, "Ava", "Taylor", 850);
INSERT INTO employees VALUES (null, "James", "Wilson", 920);
INSERT INTO employees VALUES (null, "Mia", "Garcia", 780);
INSERT INTO employees VALUES (null, "Ethan", "Jones", 880);
Concatenate Columns values Examples
The following query returns the full name (last name + first name) and salary for all employees.
Note: We add an extra space between first name and last name.
Example 1
SELECT CONCAT(first_name, ' ', last_name) AS 'Full Name', salary AS 'Salary'
FROM employees;
Output
| Full Name | Salary |
|---|---|
| Emily Johnson | 800.00 |
| Benjamin Anderson | 750.00 |
| Olivia Smith | 900.00 |
| William Davis | 845.00 |
| Sophia Brown | 1000.00 |
| Daniel Martinez | 700.00 |
| Ava Taylor | 850.00 |
| James Wilson | 920.00 |
| Mia Garcia | 780.00 |
| Ethan Jones | 880.00 |
The following query concatenate all employees data together in addition to other strings.
Example 2
SELECT CONCAT(id, '- ', first_name, ' ', last_name, ' salary is $', salary) AS 'Employees Salaries'
FROM employees;
Output
| Employees Salaries |
|---|
| 1- Emily Johnson salary is $800.00 |
| 2- Benjamin Anderson salary is $750.00 |
| 3- Olivia Smith salary is $900.00 |
| 4- William Davis salary is $845.00 |
| 5- Sophia Brown salary is $1000.00 |
| 6- Daniel Martinez salary is $700.00 |
| 7- Ava Taylor salary is $850.00 |
| 8- James Wilson salary is $920.00 |
| 9- Mia Garcia salary is $780.00 |
| 10- Ethan Jones salary is $880.00 |
Concatenate Data Exercise
1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.
2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:
id INT NOT NULL PRIMARY KEY AUTO_INCREMENTfirst_name VARCHAR(50)last_name VARCHAR(50)phone VARCHAR(20)
3- Write a query that inserts 5 rows in the "customers" table.
4- Write a query that selects all records from the "customers" table.
Note: Columns names should be returned as ID, Name (which is the full name), Phone.
Solution
DROP DATABASE IF EXISTS training;
CREATE DATABASE training;
USE training;
CREATE TABLE IF NOT EXISTS customers (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50),
last_name VARCHAR(50),
phone VARCHAR(20)
);
INSERT INTO customers (first_name, last_name, phone) VALUES
('Jack', 'Reed', '+4420123456'),
('Emma', 'Clark', '+4420551025'),
('Luke', 'Lee', '+4420102044'),
('Jim', 'Wilson', '+4420119043'),
('Lily', 'Brooks', '+4420203458');
SELECT id AS 'ID', CONCAT(first_name, ' ', last_name) AS 'Name', phone AS 'Phone'
FROM customers;
