Back to list Standard SQL How to Concatenate Strings in SQL Database: SQL MySQL PostgreSQL Oracle SQLite Operators:CONCAT Problem: You want to join strings from two columns of a table into one. Example: Our database has a table named student with data in the following columns: id, first_name and last_name. idfirst_namelast_name 1LoraSmith 2EmilBrown 3AlexJackson 4MartinDavis Let’s append the first name to the last name of the student in one string. Use a space between each name. Solution: SELECT first_name || ‘ ‘ || last_name AS full_name FROM student; This query returns records in one column named full_name: full_name Lora Smith Emil Brown Alex Jackson Martin Davis Discussion: To append a string to another and return one result, use the || operator. This adds two strings from the left and right together and returns one result. If you use the name of the column, don’t enclose it in quotes. However, in using a string value as a space or text, enclose it in quotes. In our example, we added a space to first_name and then the column last_name. This new column is called full_name. You can also use a special function: CONCAT. It takes a list of strings or names of columns to join as arguments: SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM student; The results are identical. However, the CONCAT() function is better for retrieving data from a column with NULL values. Why? Because, when a NULL is included in the values to be joined, the operator returns NULL as a result. In the case of CONCAT(), NULL will not be displayed. Look at the result of the || operator if Emill doesn’t have a last name recorded: SELECT first_name || ‘ ‘ || last_name AS full_name FROM student; full_name Lora Smith NULL Alex Jackson Martin Davis Look at the CONCAT function for the same data: SELECT CONCAT(first_name, ‘ ‘, last_name) AS full_name FROM student; full_name Lora Smith Emil Alex Jackson Martin Davis Recommended courses: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: Extracting Data From a String: SPLIT_PART in PostgreSQL How to Remove Junk Characters in SQL 5 SQL Functions for Manipulating Strings See also: How to Trim Strings in SQL How to Replace Part of a String in SQL Tags: SQL MySQL PostgreSQL Oracle SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.