|| (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 varchar(40),
   Team varchar(40),
   Number tinyint,
   Position varchar(2),
   Age tinyint,
   Height varchar(4),
   Weight real,
   College varchar(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" :: VARCHAR(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" :: VARCHAR(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