PostgreSQL CONCAT() function

Last update on July 20 2024 09:45:07 (UTC/GMT +8 hours)

CONCAT() function

The 'CONCAT()' function in PostgreSQL is a powerful tool for combining multiple strings into one. This function is particularly useful when generating full names, constructing URLs, or formatting output for reports. Understanding how to use 'CONCAT()' effectively can enhance your SQL skills and improve data presentation.

The 'CONCAT()' function can accept various data types, including integers and other numeric values, which it converts to strings automatically. The CONCAT() function automatically ignores any NULL values in its arguments. This feature allows for seamless integration of different types of data. For example, when concatenating a string with an integer, PostgreSQL automatically converts the integer to a string.

Uses of CONCAT() Function

Combining Strings: Merge multiple strings into one continuous string.

Generating Full Names: Concatenate first and last names for display purposes.

Constructing URLs: Create complete URLs from various parts like domain, path, and query parameters.

Formatting Output: Generate formatted output for reports by combining different pieces of data.

Dynamic String Creation: Build dynamic strings for use in SQL queries and applications.

Handling NULL Values: Automatically ignore NULL values while concatenating, ensuring smooth data integration.

Column and Literal Combination: Combine column values with literals or other column values for enhanced data presentation.

Syntax:

concat(<string1>,[<string2>,<string3>,...])

string1, string2, string3, ...: The strings to be concatenated. These can be column names, literals, or expressions.

PostgreSQL Version : 9.3

Pictorial Presentation of PostgreSQL CONCAT() function

Example: PostgreSQL CONCAT() function

In the example below the strings specified in the argument have been concatenated and returns a string.


-- This SELECT statement uses the CONCAT() function to concatenate multiple strings and a number
-- The arguments provided are: 'w', 3, 'r', 'esource', '.', and 'com'
-- CONCAT() will combine these arguments into a single string
SELECT concat('w', 3, 'r', 'esource', '.', 'com');
-- The expected result of this query will be the concatenated string 'w3resource.com'
-- CONCAT() function ignores any NULL values in its arguments, but in this case, there are no NULLs

Explanation:

The given SQL query uses the CONCAT() function in PostgreSQL to concatenate the strings 'w', 'r', 'esource', '.', and 'com' with the number 3.

The function combines these arguments into a single string, resulting in 'w3resource.com'.

The CONCAT() function effectively merges all the provided arguments into one continuous string.

Sample Output:

    concat
----------------
 w3resource.com
(1 row)

Example with NULL Values

In the example below, the concat function ignore the NULL and displays the result.


-- This SELECT statement uses the CONCAT() function to concatenate multiple strings and a number
-- The arguments provided are: 'w', 3, 'r', 'esource', NULL, '.', and 'com'
-- CONCAT() will combine these arguments into a single string, ignoring the NULL value
SELECT concat('w', 3, 'r', 'esource', NULL, '.', 'com');
-- The expected result of this query will be the concatenated string 'w3resource.com'
-- The NULL value in the arguments is ignored by the CONCAT() function

Explanation:

The given SQL query uses the CONCAT() function in PostgreSQL to concatenate the strings 'w', 'r', 'esource', '.', and 'com' with the number 3, while ignoring the NULL value in the arguments.

The function combines the non-NULL arguments into a single string, resulting in 'w3resource.com'.

The CONCAT() function effectively merges all the provided arguments into one continuous string, excluding any NULL values.

Sample Output:

    concat
----------------
 w3resource.com
(1 row)

Example Using Columns

Sample Table: employees

If we want to display the first_name, last_name, and Name of the employee for those employees who belongs to the department which ID is 100 from employees table the following statement can be executed.


-- This SELECT statement retrieves the employee_id, first_name, and last_name columns from the employees table
-- It also uses the CONCAT() function to combine first_name and last_name into a single string with a space in between
-- The combined string is given the alias "Name of the Employee"
-- The query filters the results to only include employees who belong to the department with an ID of 100
SELECT employee_id, first_name, last_name,
concat(first_name, ' ', last_name) AS "Name of the Employee"
FROM employees
WHERE department_id = 100;
-- The expected result will include the employee ID, first name, last name, and the concatenated full name of each employee
-- Only employees from department 100 will be included in the result set

Explanation:

This SQL query retrieves the employee_id, first_name, and last_name columns from the employees table for employees in department 100.

It uses the CONCAT() function to combine the first_name and last_name into a single string, separated by a space, and labels this concatenated string as "Name of the Employee".

The result set includes these columns for each employee in the specified department.

Sample Output:

 employee_id | first_name  | last_name | Name of the Employee
-------------+-------------+-----------+----------------------
         108 | Nancy       | Greenberg | Nancy Greenberg
         109 | Daniel      | Faviet    | Daniel Faviet
         110 | John        | Chen      | John Chen
         111 | Ismael      | Sciarra   | Ismael Sciarra
         112 | Jose Manuel | Urman     | Jose Manuel Urman
         113 | Luis        | Popp      | Luis Popp
(6 rows)

Combining Columns with Literals

Sample Table: employees

To filter the results to only include employees who belong to the department with an ID of 100, the following statement can be used:


-- It uses the CONCAT() function to combine the first_name, last_name, and employee_id into a single string
-- The combined string includes the first name, last name, and employee ID in the format: "First Last (ID: EmployeeID)"
-- The concatenated string is given the alias "Employee Details"
SELECT CONCAT(first_name, ' ', last_name, ' (ID: ', employee_id, ')') AS "Employee Details"
FROM employees
WHERE department_id = 100;
-- The expected result will include a single column "Employee Details" with the full name and ID of each employee
-- Only employees from department 100 will be included in the result set

Explanation:

This SQL query retrieves employee details from the employees table for those in department 100.

It uses the CONCAT() function to combine the first_name, last_name, and employee_id into a single string, formatted as "First Last (ID: EmployeeID)", and labels this concatenated string as "Employee Details".

The result set includes this detailed information for each employee in the specified department.

Sample Output:

Employee Details           |
---------------------------+
Nancy Greenberg (ID: 108)  |
Daniel Faviet (ID: 109)    |
John Chen (ID: 110)        |
Ismael Sciarra (ID: 111)   |
Jose Manuel Urman (ID: 112)|
Luis Popp (ID: 113)        |

Using CONCAT() in Complex Expressions

Sample Table: employees

Sample Table: job_history

To display employee names and their annual salaries from joined employees and job_history tables, the following statement can be used:


-- It uses the CONCAT() function to combine the first_name, last_name, and salary into a single string
-- The combined string includes the first name, last name, and salary in the format: "First Last earns $Salary annually"
-- The concatenated string is given the alias "Employee Salary"
-- The query performs an INNER JOIN on the employees and job_history tables using the employee_id column
SELECT CONCAT(first_name, ' ', last_name, ' earns $', salary, ' annually') AS "Employee Salary"
FROM employees
JOIN job_history ON employees.employee_id = job_history.employee_id;
-- The expected result will include a single column "Employee Salary" with the full name and salary of each employee
-- The INNER JOIN ensures that only matching records from both tables are included in the result set

Explanation:

This SQL query retrieves the salary details of employees by joining the employees and job_history tables on the employee_id column.

It uses the CONCAT() function to create a single string that combines the first_name, last_name, and salary of each employee, formatted as "First Last earns $Salary annually", and labels this concatenated string as "Employee Salary".

The result set includes this detailed salary information for each employee.

Sample Output:

Employee Salary                           |
------------------------------------------+
Neena Kochhar earns $17000.00 annually    |
Neena Kochhar earns $17000.00 annually    |
Lex De Haan earns $17000.00 annually      |
Den Raphaely earns $11000.00 annually     |
Payam Kaufling earns $7900.00 annually    |
Jonathon Taylor earns $8600.00 annually   |
Jonathon Taylor earns $8600.00 annually   |
Jennifer Whalen earns $4400.00 annually   |
Jennifer Whalen earns $4400.00 annually   |
Michael Hartstein earns $13000.00 annually|

Frequently Asked Questions (FAQ) - PostgreSQL CONCAT() Function

1. What is the PostgreSQL CONCAT() function?

The CONCAT() function in PostgreSQL is used to combine multiple strings into one. It is useful for generating full names, constructing URLs, or formatting output for reports.

2. What data types does the PostgreSQL CONCAT() function accept?

The CONCAT() function can accept various data types, including integers and other numeric values, which it converts to strings automatically.

3. Does the PostgreSQL CONCAT() function handle NULL values?

Yes, the CONCAT() function automatically ignores any NULL values in its arguments, allowing for seamless integration of different types of data.

4. How does PostgreSQL handle numeric values in CONCAT()?

When concatenating a string with an integer or any numeric value, PostgreSQL automatically converts the numeric value to a string.

5. Which PostgreSQL versions support the CONCAT() function?

The CONCAT() function is available in PostgreSQL version 9.3 and later.

6. What is a typical use case for the PostgreSQL CONCAT() function?

A typical use case for the CONCAT() function includes combining first and last names, formatting addresses, or creating dynamic strings for output in reports and web applications.

Previous: CHR function
Next: INITCAP function