Concatenation and Temporal Operators in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Concatenation and Temporal Operators in MySQL

In this article, I am going to discuss Concatenation and Temporal Operators in MySQL with Examples. Please read our previous article where we discussed Arithmetic Operators in MySQL with examples.

Concatenation Operator in MySQL

The Concatenation Operator in MySQL is the only operator which is used only for strings. The concatenation operator joins two strings together. In MySQL, multiple strings are joined together using the CONCAT function.

CONCAT function syntax in MySQL:

Following is the syntax to use the CONCAT function in MySQL. The CONCAT function can take n number of strings as arguments and return a single string as a result by adding all the input strings.

CONCAT (string1, string2, string3, …)

1. The CONCAT function may take one or more arguments.
2. It returns a single string as a result by concatenating all arguments.
3. The CONCAT function returns a nonbinary string if all arguments are nonbinary strings.
4. The CONCAT function returns a binary string if the arguments include any binary strings.
5. If the argument is numeric, it is converted to its equivalent nonbinary string form.
6. Returns NULL if any argument is NULL.

Example of MySQL CONCAT Function using One argument
SELECT CONCAT(‘MySQL’);
Output: MySQL

Example of MySQL CONCAT Function using two arguments
SELECT CONCAT(‘MySQL’, ‘Tutorials’);
Output: MySQLTutorials

Example of MySQL CONCAT Function using three arguments
SELECT CONCAT(‘MySQL’, ‘ ‘, ‘Tutorials’);
Output: MySQL Tutorials

Example of CONCAT Function using Null Argument
SELECT CONCAT(‘MySQL’, NULL);
Output: null

Let us understand the Concatenation Operator in MySQL with some examples using database table columns. We are going to use the below Employee table to understand Concatenation Operator.

Please use the below SQL Script to create the Company database and the Employee table with the required data.

CREATE DATABASE Company;
USE Company;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  FirstName VARCHAR(45) NOT NULL,
  LastName VARCHAR(45) NOT NULL,
  Department VARCHAR(45) NOT NULL,
  Salary FLOAT NOT NULL,
  Gender VARCHAR(45) NOT NULL,
  Age INT NOT NULL,
  City VARCHAR(45) NOT NULL
);

INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'Doe', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1002, 'Mary', 'Smith', 'HR', 45000, 'Female', 27, 'London');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Brown', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Walker', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'Jones', 'HR', 75000, 'Female', 26, 'London');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1006, 'Anurag', 'Mohanty', 'IT', 35000, 'Male', 25, 'Mumbai');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1007, 'Priyanla', 'Dewangan', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1008, 'Sambit', 'Mohanty', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1009, 'Pranaya', 'Rout', 'IT', 50000, 'Male', 28, 'Mumbai');
INSERT INTO Employee (Id, FirstName, LastName, Department, Salary, Gender, Age, City) VALUES (1010, 'Hina', 'Sharma', 'HR', 75000, 'Female', 26, 'Mumbai');

Example: CONCAT using a database table

Currently, in the Employee table, we have two separate columns to store the employee’s name i.e. FirstName and LastName. But our business requirement is to fetch the employee’s name as FullName which should include the First and Last name of the employee. The following SQL Script will return the FullName which includes the FirstName and LastName. Here, we are using CONCAT built-in string function to join FirstName and LastName.

SELECT Id, Department, CONCAT(FirstName, ‘ ‘ , LastName) AS Fullname FROM Employee;

Once you execute the above query, you will get the following result set.

Temporal Operator in MySQL

Intervals are used while using date and time in MySQL. For example, A week means today +7 days. Here, the plus sign is used in the temporal calculation and called a temporal operator. Here is an example of a Temporal operator in MySQL.

SELECT (CURRENT_DATE – INTERVAL 7 DAY) AS date;

Now execute the above SQL statement, and you will get the following date.

Here, CURRENT_DATE represents the currents time on the server.

The following SQL statement is to display tomorrow’s date.

SELECT (CURRENT_DATE + INTERVAL 1 DAY) AS TomorrowDate;

Now execute the SQL statement, and you will get tomorrow’s date.

In the next article, I am going to discuss UNION, UNION ALL, INTERSECT, and EXCEPT SET Operators in MySQL with Examples. Here, in this article, I try to explain Concatenation and Temporal Operators in MySQL with Examples. I hope you enjoy this Concatenation and Temporal Operators in MySQL article.