`||` (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.
SQream DB 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(40),
   Team text(40),
   Number tinyint,
   Position text(2),
   Age tinyint,
   Height text(4),
   Weight real,
   College text(40),
   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(2)) || "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(2) || (' ' || "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

|| (Concatenate)