Yogesh Chauhan's Blog

CONCAT and CONCAT_WS Functions in Postgres

in Postgres on April 25, 2020

Use the string concatenation operator || to concatenate two or more strings or even a sting with an integer and so on.

String concatenation


'Post' || 'greSQL'	=> PostgreSQL

String concatenation with one non-string input


'Value: ' || 42	=> Value: 42

Full example:


SELECT address || ',' || city || ',' || postal_code FROM customers;

//Output
"Obere Str. 57,Berlin,12209"
"Avda. de la Constitución 2222,México D.F.,05021"
"Mataderos  2312,México D.F.,05023"
"120 Hanover Sq.,London,WA1 1DP"
.....

If you concatenate a string with a NULL value then it will return a NULL value.

For example:


SELECT address || ',' || city || ',' || postal_code || NULL FROM customers;

//Output
[null]
[null]
[null]
[null]
....

CONCAT function

Since version 9.1, PostgreSQL introduced a built-in string function named CONCAT to concatenate two or more strings into one.

Syntax:


concat(str "any" [, str "any" [, ...] ])

It concatenates all arguments. NULL arguments are ignored. You can pass array as well.

For example:


concat('abcde', 2, NULL, 22)	=> abcde222

CONCAT_WS function

WS stands for with separator.

Synatx:


concat_ws(seperator text, str "any" [, str "any" [, ...] ])

It concatenates all but first arguments with separators. The first parameter is used as a separator.

NULL arguments are ignored in this function as well.

For example:


concat_ws(',', 'abcde', 2, NULL, 22)  =>	abcde,2,22

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to create two segues with two UIButtons on a single page (Swift 5.0)?SwiftHow to Sort (Shuffle) an Array in Random Order in JavaScript?JavaScript12 URLSearchParams methods in JavaScriptJavaScriptWhat does JSX do in React?ReactLearn to Implement Estimated Reading Time using PHP Part 1: The BasicsPHPHow to add Local State to a Class in React?React