SQLite concat_ws() Function

Summary: in this tutorial, you will learn how to use the SQLite concat_ws() function to concatenate strings into a single string with a specified separator.

Introduction to the SQLite concat_ws() function

In SQLite, the concat_ws() function allows you to concatenate multiple strings into a single string using a specified separator.

Here’s the syntax of the concat_ws() function:

concate_ws(separator, str1, str2, ...);Code language: SQL (Structured Query Language) (sql)

The concat_ws() function returns a concatenation of the str1, str2, … with the specified separator.

If the separator is NULL, the concat() function returns NULL.

If all the input strings (str1, str2, …) are NULL, the concat_ws() function returns an empty string.

If any input string is NULL, the concat_ws() function uses an empty string for concatenation.

SQLite concat_ws() function examples

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

1) Basic SQLite concat_ws() function examples

The following example uses the concat_ws() function to concatenate the first and last names with a separator:

SELECT
  CONCAT_WS (', ', 'John', 'Doe') name;Code language: SQL (Structured Query Language) (sql)

Output:

name
---------
John, DoeCode language: SQL (Structured Query Language) (sql)

The following example uses the concat_ws() function to concatenate multiple strings into a single string and return NULL because the separator is NULL:

SELECT
  CONCAT_WS (NULL, 'Jane', 'Doe') name;Code language: SQL (Structured Query Language) (sql)

Output:

name
----
nullCode language: SQL (Structured Query Language) (sql)

The following example illustrates how the concat_ws() function ignores NULL input string when concatenating them:

SELECT
  CONCAT_WS (', ', 'A', NULL, 'B', 'C') alphabet;Code language: SQL (Structured Query Language) (sql)

Output:

alphabet
--------
A, B, CCode language: SQL (Structured Query Language) (sql)

2) Using concat_ws() function with table data

We’ll use the emloyees table from the sample database:

The following example uses the concat_ws() function to concatenate the first and last names of employees using the space separator:

SELECT
  CONCAT_WS (' ', FirstName, LastName) AS FullName
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

FullName
----------------
Andrew Adams
Nancy Edwards
Jane Peacock
Margaret Park
Steve Johnson
Michael Mitchell
Robert King
Laura CallahanCode language: SQL (Structured Query Language) (sql)

Summary

Use the SQLite concat_ws() to concatenate multiple strings into a single string using a specified separator.

Was this tutorial helpful ?