Concatenating First Name and Last Name in SQL
Explanation
Concatenation in SQL means joining two or more string values together. To concatenate first name and last name columns, we can use:
- The
CONCAT()
function (standard SQL) - The concatenation operator
||
(in some databases like PostgreSQL, Oracle) - The
+
operator (in SQL Server)
We'll also typically want to add a space between the first and last names.
Example with Dummy Data
Let's create a dummy table called employees
with first_name and last_name columns, then write a query to concatenate them.
-- Create a table with dummy data
CREATE TABLE employees (
id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
-- Insert dummy data
INSERT INTO employees (id, first_name, last_name) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Robert', 'Johnson'),
(4, 'Emily', 'Davis'),
(5, 'Michael', 'Wilson');
-- Query to concatenate first and last names
SELECT
id,
first_name,
last_name,
CONCAT(first_name, ' ', last_name) AS full_name
FROM
employees;
Output
The output of the above query would be:
id | first_name | last_name | full_name |
---|---|---|---|
1 | John | Doe | John Doe |
2 | Jane | Smith | Jane Smith |
3 | Robert | Johnson | Robert Johnson |
4 | Emily | Davis | Emily Davis |
5 | Michael | Wilson | Michael Wilson |
- Using
||
(PostgreSQL, Oracle):
SELECT first_name || ' ' || last_name AS full_name FROM employees;
- Using
+
(SQL Server):
SELECT first_name + ' ' + last_name AS full_name FROM employees;
- Using
CONCAT_WS()
(for multiple values with a separator):
-- CONCAT_WS adds the separator between each non-null value
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name FROM employees;
Handling NULL Values
If either first_name or last_name could be NULL, you might want to use:
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name
FROM employees;
This ensures you don't get NULL results if one of the names is missing.
At Online Learner, we're on a mission to ignite a passion for learning and empower individuals to reach their full potential. Founded by a team of dedicated educators and industry experts, our platform is designed to provide accessible and engaging educational resources for learners of all ages and backgrounds.
Terms Disclaimer About Us Contact Us
Copyright 2023-2025 © All rights reserved.