SQLite concat() Function

Summary: in this tutorial, you will learn how to use the concat() function and concatenation operator (||) and to concatenate two strings into a string.

Introduction to SQLite concat() function

In SQLite, the concat() function allows you to concatenate multiple strings into a single string. Here’s the syntax of the concat() function:

concat(s1, s2, ...);

In this syntax:

• s1, s2, .. are the strings that you want to concatenate.

The concat() function returns a string that is the result of concatenating the input strings.

The concat() function treats NULL as an empty string. If all the input strings are NULLs, the concat() function returns an empty string.

SQLite concat() function examples

Let’s take some examples of using the concat() function.

1) Basic concat() function example

The following example uses the concat() function to concatenate two strings into a string:

SELECT concat('SQLite', ' Concat') result;Code language: JavaScript (javascript)

Output:

result
-------------
SQLite Concat

The following statement uses the concat() function to concatenate three strings into a single string:

SELECT concat('SQLite',' ','Concat') result;Code language: JavaScript (javascript)

Output:

result
-------------
SQLite Concat

2) Using concat() function with table data

We’ll use the employees table from the sample database.

The following example uses the concat() function to concatenate the first name, space, and last name of employees to form full names:

SELECT
    concat(FirstName,' ', LastName) FullName
FROM
    Employees
ORDER BY
    FullName;Code language: JavaScript (javascript)

Output:

FullName
----------------
Andrew Adams
Jane Peacock
Laura Callahan
Margaret Park
Michael Mitchell
Nancy Edwards
Robert King
Steve Johnson

SQLite concatenation operator (||)

Besides the concat() function, SQLite provides the concatenation operator (||) to join two strings into one string.

Here’s the syntax of the concatenation operator:

s1 || s2Code language: SQL (Structured Query Language) (sql)

It is possible to use multiple concatenation operators in the same expression:

s1 || s2 || s3Code language: SQL (Structured Query Language) (sql)

The following example shows how to concatenate two literal strings into one:

SELECT 'SQLite ' || 'concat' result;Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
-------------
SQLite concatCode language: SQL (Structured Query Language) (sql)

The following example illustrates how to use two concatenation operators in the same expression:

SELECT 'SQLite' || ' ' || 'concat' result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-------------
SQLite concatCode language: SQL (Structured Query Language) (sql)

This example shows how to construct the full name of employees from the first name, space, and last name:

SELECT
    FirstName || ' ' || LastName AS FullName
FROM
    Employees
ORDER BY
    FullName;Code language: SQL (Structured Query Language) (sql)

Output:

FullName
----------------
Andrew Adams
Jane Peacock
Laura Callahan
Margaret Park
Michael Mitchell
Nancy Edwards
Robert King
Steve Johnson

Summary

• Use the concat() function to concatenate multiple strings into a single string.
• Use the concatenation operator (||) to join multiple strings into one string.