String concatenation does not work in SQLite

Question

I am trying to execute a SQlite replace function, but use another field in the function.

select  locationname + '<p>' from location;

In this snip, the result is a list of 0s. I would have expected a string with the text from locationname and the '<p>' literals.

score 274 · Accepted Answer · answered Aug 25 '10 at 17:54

274

Try using || in place of +

select  locationname || '<p>' from location;

From SQLite documentation:

The || operator is "concatenate" - it joins together the two strings of its operands.

answered Aug 25 '10 at 17:54

codaddict

445,704
82
492
529

32

field1 || field2 returns null if one of the fields is null. One might want to do ifnull(field1,'')||ifnull(field2,''). That will give you a response if one or both fields are null. Then you get to figure out what you want to do if both were null. – Tom Cerul Oct 06 '11 at 14:55
6

@TomCerul or use `COALESCE(nullable_field, '') || COALESCE(another_nullable_field, '')` – zx8754 Sep 03 '14 at 13:51
The importance of TomCerul's comment is worthy rewriting or at least editing the correct answer. – Pepik Jan 24 '23 at 14:34

score 42 · Answer 2 · answered Aug 25 '10 at 17:55

42

The || operator is the concatenation in SQLite. Use this code:

select  locationname || '<p>' from location;

answered Aug 25 '10 at 17:55

shamittomar

46,210
12
74
78

11

The double pipe is also the ANSI method of concatenating strings, supported on Oracle & PostgreSQL too... – OMG Ponies Aug 25 '10 at 18:04

Brian Burns · Answer 3 · 2017-12-17T03:12:49.170

37

For comparison,

SQLite                      ||  
Oracle                      CONCAT(string1, string2) or ||
MySQL                       CONCAT(string1, string2, string3...) or || if PIPES_AS_CONCAT enabled
Postgres                    CONCAT(string1, string2, string3...) or ||
Microsoft SQL Server 2012+  CONCAT(string1, string2, string3...) or + 
Microsoft Access            +

edited Dec 17 '17 at 03:12

answered Jun 14 '16 at 11:43

Brian Burns

20,575
8
83
77

2

Starting in 2012, SQL Server also supports `CONCAT(string1, string2, string3...)` – Tim Cooke Nov 22 '16 at 09:15
2

MySQL also supports `||` if `PIPES_AS_CONCAT` mode is enabled. – Paul Spiegel Dec 16 '17 at 14:50

score 2 · Answer 4 · answered Dec 06 '11 at 00:37

2

for Visual Studio 2010, using the Data Sources designer or wizard, you're in trouble using || operator. Create a view in the sqlite db and create your data source(s) from that.

String concatenation does not work in SQLite

4 Answers4

Linked

Related