{"id":10971,"date":"2023-10-10T19:12:24","date_gmt":"2023-10-11T02:12:24","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=10971"},"modified":"2023-10-12T21:22:46","modified_gmt":"2023-10-13T04:22:46","slug":"mysql-concat","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-string-functions\/mysql-concat\/","title":{"rendered":"MySQL CONCAT() Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL CONCAT()<\/code> function to concatenate multiple strings into a single string.<\/p>\n\n\n\n

Introduction to the MySQL CONCAT function<\/h2>\n\n\n\n

The CONCAT()<\/code> function allows you to concatenate multiple strings into a single string. <\/p>\n\n\n\n

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

CONCAT(string1, string2, string3, ...)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The CONCAT()<\/code> function accepts a variable number of input strings: string1<\/code>, string2<\/code>, string3<\/code>, … <\/p>\n\n\n\n
It returns a single string that combines the string arguments string1<\/code>, string2<\/code>, and string3<\/code> … <\/p>\n\n\n\n
If any string is NULL<\/code>, the CONCAT()<\/code> function returns NULL<\/code>.<\/p>\n\n\n\n
In addition, if you pass numbers to the CONCAT()<\/code> function, it’ll convert these numbers to their equivalent strings before concatenation.<\/p>\n\n\n\n
MySQL CONCAT function examples<\/h2>\n\n\n\n
Let’s take some examples of using the CONCAT()<\/code> function.<\/p>\n\n\n\n
1) Simple CONCAT function example<\/h3>\n\n\n\n
The following example uses the CONCAT()<\/code> function to concatenate the quoted string into a single string:<\/p>\n\n\n
SELECT<\/span> \n  CONCAT<\/span>('John'<\/span>, ' '<\/span>, 'Doe'<\/span>) full_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------+<\/span>\n| full_name |\n+-----------+<\/span>\n| John Doe  |\n+-----------+<\/span>\n1 row in set<\/span> (0.01<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we use the CONCAT()<\/code> function to combine three quoted strings ‘John’, ‘ ‘, and ‘Doe’ into the full name.<\/p>\n\n\n\n
If you only concatenate the quoted strings, you can concatenate them by placing the strings next to each other like this:<\/p>\n\n\n
SELECT<\/span> 'John'<\/span>  ' '<\/span> 'Doe'<\/span> full_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-----------+<\/span>\n| full_name |\n+-----------+<\/span>\n| John Doe  |\n+-----------+<\/span>\n1 row in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Using MySQL CONCAT with table data<\/h3>\n\n\n\n
We’ll use the employees<\/code> table from the sample database<\/a>:<\/p>\n\n\n\n
\"employees<\/figure>\n\n\n\n
The following example uses the CONCAT<\/code> function to concatenate the first name, space, and last name into the full name:<\/p>\n\n\n
SELECT<\/span> \n  CONCAT<\/span>(firstName, ' '<\/span>, lastName) full_name \nFROM<\/span> \n  employees;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------------+<\/span>\n| full_name         |\n+-------------------+<\/span>\n| Diane Murphy      |\n| Mary Patterson    |\n| Jeff Firrelli     |\n| William Patterson |\n| Gerard Bondur     |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Using MySQL CONCAT with NULL values<\/h3>\n\n\n\n
We’ll take the customers<\/code> table for the demonstration:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following example uses the CONCAT<\/code> function to concatenate the values in the city and state columns into a single string:<\/p>\n\n\n
SELECT<\/span> \n  city, \n  state, \n  CONCAT<\/span>(city, ','<\/span>, state) city_state \nFROM<\/span> \n  customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------------+---------------+---------------------------+<\/span>\n| city              | state         | city_state                |\n+-------------------+---------------+---------------------------+<\/span>\n| Nantes            | NULL          | NULL                      |\n| Las Vegas         | NV            | Las Vegas,NV              |\n| Melbourne         | Victoria      | Melbourne,Victoria        |\n| Nantes            | NULL          | NULL                      |\n| Stavern           | NULL          | NULL                      |\n| San Rafael        | CA            | San Rafael,CA             |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that if the state is NULL<\/code>, the result string will be NULL<\/code>. To handle the NULL<\/code> values gracefully, you can use the CONCAT_WS<\/code> function.<\/p>\n\n\n\n
SELECT<\/span> \n  city, \n  state, \n  CONCAT<\/span>(\n    city, \n    IF<\/span>(state IS<\/span> NULL<\/span>, ''<\/span>, ','<\/span>), \n    IFNULL<\/span>(state, ''<\/span>)\n  ) city_state \nFROM<\/span> \n  customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
+-------------------+---------------+---------------------------+<\/span>\n| city              | state         | city_state                |\n+-------------------+---------------+---------------------------+<\/span>\n| Nantes            | NULL          | Nantes                    |\n| Las Vegas         | NV            | Las Vegas,NV              |\n| Melbourne         | Victoria      | Melbourne,Victoria        |\n| Nantes            | NULL          | Nantes                    |\n| Stavern           | NULL          | Stavern                   |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the CONCAT()<\/code> takes three arguments:<\/p>\n\n\n\n
    \n
  • city<\/code>: The first argument.<\/li>\n\n\n\n
  • IF(state IS NULL, '', ',')<\/code> : The second argument returns space if the state is NULL<\/code> or a comma (,) otherwise.<\/li>\n\n\n\n
  • IFNULL(state, '')<\/code>: The third argument returns a space if the state is NULL<\/code> or state otherwise.<\/li>\n<\/ul>\n\n\n\n
    The result string will be city only if the state is NULL<\/code> or city, state if the state is not  NULL<\/code>.<\/p>\n\n\n\n
    The CONCAT_WS<\/code> function concatenates multiple strings into a string delimited by a specified space. It skips the NULL<\/code> values. For example:<\/p>\n\n\n
    SELECT<\/span> \n  city, \n  state, \n  CONCAT_WS<\/span>(','<\/span>, city, state) city_state \nFROM<\/span> \n  customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-------------------+---------------+---------------------------+<\/span>\n| city              | state         | city_state                |\n+-------------------+---------------+---------------------------+<\/span>\n| Nantes            | NULL          | Nantes                    |\n| Las Vegas         | NV            | Las Vegas,NV              |\n| Melbourne         | Victoria      | Melbourne,Victoria        |\n| Nantes            | NULL          | Nantes                    |\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL CONCAT()<\/code> function to concatenate multiple strings into a single string.<\/li>\n\n\n\n
    • The CONCAT()<\/code> function returns NULL<\/code> if any string argument is NULL<\/code>.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Alternatively, you can use null-related functions such as IF<\/a><\/code> and IFNULL<\/a><\/code>. For example:<\/p>\n\n\n