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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
4 different ways to create JavaScript ObjectsJavaScriptIntroduction to Angular modules Part 1: NgModule metadataAngularTackle Accessibility in React with JSXReactRevisiting variable scope in JavaScriptJavaScriptHow to replace HTML lists using CSS Counters?CSSHow to animate list items using CSS and JavaScript?CSS