YogeshChauhan . com

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
amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

How to vertically and horizontally align text and image block (without flex or grid) in CSS?CSSWhere is the PHP log file located on Mac OS?PHPHow to add Go Back button in Swift 5?SwiftCommon Table Expressions (CTE) in PostgreSQLPostgresUNION and UNION ALL in PostgresPostgresWhat are Web services?Misc