{"id":858,"date":"2016-07-14T09:28:58","date_gmt":"2016-07-14T09:28:58","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=858"},"modified":"2025-02-04T05:50:46","modified_gmt":"2025-02-04T12:50:46","slug":"sql-concat","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-string-functions\/sql-concat\/","title":{"rendered":"SQL CONCAT Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL CONCAT<\/code> function to concatenate two or more strings into a single string.<\/p>\n\n\n\n

Introduction to SQL CONCAT function #<\/a><\/h2>\n\n\n\n

The SQL CONCAT <\/code>function concatenates two or more strings into one string.<\/p>\n\n\n\n

Here’s the syntax of the CONCAT<\/code> function:<\/p>\n\n\n

CONCAT(string1, string2,..);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To concatenate multiple strings, you pass them as a list of comma-separated arguments to the CONCAT<\/code> function.<\/p>\n\n\n\n
The CONCAT<\/code> function returns a string which is the combination of the input strings. It returns NULL<\/code> if one of the arguments is NULL<\/code>.<\/p>\n\n\n\n
Most database systems support the CONCAT<\/code> function with some differences among them.<\/p>\n\n\n\n
For example, the CONCAT<\/code> function in MySQL allows you to concatenate more than two strings, whereas the CONCAT<\/code> function in Oracle Database concatenates exactly two strings.<\/p>\n\n\n\n
Besides using the CONCAT<\/code> function, you can use the concatenation operator, e.g., in Oracle and PostgreSQL, you can use the ||<\/code> operator to concatenate two or more strings. In Microsoft SQL Server, you use the +<\/code> operator.<\/p>\n\n\n\n
SQL CONCAT function examples #<\/a><\/h2>\n\n\n\n
The following statement uses the CONCAT<\/code> function to concatenate two strings:<\/p>\n\n\n
SELECT<\/span> CONCAT<\/span>('SQL CONCAT function'<\/span>, ' demo'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        concat\n----------------------<\/span>\n SQL CONCAT function demo\n(1 row)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement uses the CONCAT<\/code> function to return the full name of the employees by concatenating the first name, space, and last name.<\/p>\n\n\n
SELECT<\/span>\n  CONCAT<\/span>(first_name, ' '<\/span>, last_name) AS<\/span> name<\/span>\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
If you are using the Oracle Database, you have to apply the CONCAT<\/code> function twice the achieve the same result:<\/p>\n\n\n
SELECT<\/span>\n  CONCAT<\/span>(CONCAT<\/span>(first_name, ' '<\/span>), last_name) AS<\/span> name<\/span>\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The inner CONCAT<\/code> function concatenates the first name with space and the outer CONCAT<\/code> function concatenates the result of the inner CONCAT<\/code> function with the last name.<\/p>\n\n\n\n
Using the concatenation operator in Oracle (and also PostgreSQL) will be much cleaner.<\/p>\n\n\n
SELECT<\/span>\n  first_name || ' '<\/span> || last_name AS<\/span> name<\/span>\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In Microsoft SQL Server, you can use the + operator to concatenate strings like this:<\/p>\n\n\n
SELECT<\/span>\n  first_name + ' '<\/span> + last_name AS<\/span> name<\/span>\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you are using MySQL or PostgreSQL, you can use the CONCAT_WS<\/code> function to concatenate strings with a separator.<\/p>\n\n\n
CONCAT_WS(separator,string1,string2,...);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can use the CONCAT_WS<\/code> function to construct the employee’s full names:<\/p>\n\n\n
SELECT<\/span>\n  CONCAT_WS<\/span>(' '<\/span>, first_name, last_name) AS<\/span> name<\/span>\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  name<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
You can use the IS NULL<\/code><\/a> operator or COALESCE<\/code><\/a> and NULLIF<\/code> functions to handle NULL<\/code> .<\/p>\n\n\n\n