{"id":112,"date":"2024-11-19T10:51:28","date_gmt":"2024-11-19T03:51:28","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=112"},"modified":"2025-02-04T13:43:51","modified_gmt":"2025-02-04T06:43:51","slug":"postgresql-concat","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-concat\/","title":{"rendered":"PostgreSQL CONCAT: Concatenating Strings"},"content":{"rendered":"\n

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

PostgreSQL concatenation operator || #<\/a><\/h2>\n\n\n\n

To concatenate two strings into a single string, you use the concatenation operator ||<\/code><\/p>\n\n\n

s1 || s2<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following SELECT<\/code> statement retrieves the product name and brand from the inventories table and concatenates them into a single string:<\/p>\n\n\n
SELECT<\/span>\n  brand || name<\/span> AS<\/span> product\nFROM<\/span>\n  inventories;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
         product\n-------------------------\n AppleiPhone 14 Pro\n SamsungGalaxy S23 Ultra\n GooglePixel 7 Pro\n SonyXperia 1 IV<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
To make the information readable, we can separate the strings using a space like this:<\/p>\n\n\n
SELECT<\/span>\n  brand || ' '<\/span> || name<\/span> AS<\/span> product\nFROM<\/span>\n  inventories;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
         product\n--------------------------\n Apple iPhone 14 Pro\n Samsung Galaxy S23 Ultra\n Google Pixel 7 Pro\n Sony Xperia 1 IV<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
CONCAT function #<\/a><\/h2>\n\n\n\n
Besides the concatenation operator, PostgreSQL offers the CONCAT()<\/code> function that concatenates multiple strings into a string:<\/p>\n\n\n
CONCAT(s1, s2, ...)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can concatenate the strings from the brand<\/code> column, a space, and strings in the name<\/code> column into a single string:<\/p>\n\n\n
SELECT<\/span>\n  CONCAT(brand, ' '<\/span>, name<\/span>) AS<\/span> product\nFROM<\/span>\n  inventories;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
         product\n--------------------------\n Apple iPhone 14 Pro\n Samsung Galaxy S23 Ultra\n Google Pixel 7 Pro\n Sony Xperia 1 IV<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
CONCAT_WS: Concatenating strings with a separator #<\/a><\/h2>\n\n\n\n
To concatenate strings with a separator, you use the CONCAT_WS()<\/a><\/code> function with the following syntax:<\/p>\n\n\n
CONCAT_WS(separator, s1, s2, ..)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that WS stands for With Separator.<\/p>\n\n\n\n
For example, the following statement uses the CONCAT_WS()<\/code> function to concatenate the brand and name with the space as a separator:<\/p>\n\n\n
SELECT<\/span>\n  CONCAT_WS(' '<\/span>, brand, name<\/span>) AS<\/span> product\nFROM<\/span>\n  inventories;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
         product\n--------------------------\n Apple iPhone 14 Pro\n Samsung Galaxy S23 Ultra\n Google Pixel 7 Pro\n Sony Xperia 1 IV<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/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