|| (Concatenate)

Concatenate two strings to create a longer string

Syntax

expr1 || expr2

Arguments

Parameter	Description
expr1, expr2	String expressions

Returns

Returns the same type as the argument supplied.

Notes

• Both values must be strings, and can’t be NULL. If NULLS are expected, use COALESCE.

• Blue removes the trailing spaces from strings by default, which may lead to unexpected results. See the examples for more information.

Examples

For these examples, assume a table named nba, with the following structure:

CREATE TABLE nba
(
   Name TEXT,
   Team TEXT,
   Number TINYINT,
   Position TEXT,
   Age TINYINT,
   Height TEXT,
   Weight REAL,
   College TEXT,
   Salary FLOAT
 );

Here’s a peek at the table contents (Download nba.csv):

nba.csv

Name	Team	Number	Position	Age	Height	Weight	College	Salary
Avery Bradley	Boston Celtics	0.0	PG	25.0	6-2	180.0	Texas	7730337.0
Jae Crowder	Boston Celtics	99.0	SF	25.0	6-6	235.0	Marquette	6796117.0
John Holland	Boston Celtics	30.0	SG	27.0	6-5	205.0	Boston University
R.J. Hunter	Boston Celtics	28.0	SG	22.0	6-5	185.0	Georgia State	1148640.0
Jonas Jerebko	Boston Celtics	8.0	PF	29.0	6-10	231.0		5000000.0
Amir Johnson	Boston Celtics	90.0	PF	29.0	6-9	240.0		12000000.0
Jordan Mickey	Boston Celtics	55.0	PF	21.0	6-8	235.0	LSU	1170960.0
Kelly Olynyk	Boston Celtics	41.0	C	25.0	7-0	238.0	Gonzaga	2165160.0
Terry Rozier	Boston Celtics	12.0	PG	22.0	6-2	190.0	Louisville	1824360.0

Concatenate two values

Convert values to string types before concatenation

nba=> SELECT ("Age" :: TEXT) || "Name" FROM nba ORDER BY 1 DESC LIMIT 5;
?column?
----------------
40Tim Duncan
40Kevin Garnett
40Andre Miller
39Vince Carter
39Pablo Prigioni

Concatenate two strings

t=> SELECT 'Hello, this is' || ' nice';
?column?
-------------------
Hello, this is nice

Warning

Trailing spaces are trimmed by default. For example,

t=> SELECT 'Hello, this is ' || 'nice';
?column?
-------------------
Hello, this isnice

This may sometimes lead to an unexpected result. See the example below for a remedy.

Adding spaces

Add a space and concatenate it first to bypass the space trimming issue

nba=> SELECT ("Age" :: TEXT || (' ' || "Name")) FROM nba ORDER BY 1 DESC LIMIT 5;
?column?
-----------------
40 Tim Duncan
40 Kevin Garnett
40 Andre Miller
39 Vince Carter
39 Pablo Prigioni