How Do I Concatenate in SQLite?

SQLite is used to manage the data of a database, in which the website’s or application’s data is stored in the form of tables. Like MySQL, SQLite also supports a lot of built-in functions like max(), min(), but many functions that are supported by MySQL, not supported by SQLite.

SQLite is a database management system, which is available free of cost, and also its architecture is much simpler as compared to other database management systems. The main feature of SQLite, it is serverless, which means it uses the server of a machine or Operating System on which it is being operated, instead of using its own server.

This write-up is focussing on how to concatenate strings in SQLite. Does concatenate function work in SQLite? Let’s find out.

What is concatenate in SQLite

The word concatenate means to join different things in a series or chain, MySQL supports a built-in function of concat() which is used to join the strings together and form a single string.

Unfortunately, this built-in function of concat() is not supported by the SQLite but we can concatenate the two strings in SQLite by using the concatenate operator which is ” || “.  The general syntax of using the concatenate operators in SQLite is:

SELECT "string1" || " " || "string2";

The explanation of this syntax is:

  • Use the SELECT clause to retrieve the data
  • Replace the string1 with the string, which  you want to concatenate
  • Put the or operators || to concatenate with the next thing
  • Put space if you want to add space between the two strings
  • Replace the string2 with the string, to which you want to concatenate

How to concatenate strings in SQLite

Consider an example to understand this syntax of concatenating two strings, we have two strings, like “John” and the “Paul”, we want to concatenate these two in SQLite as:

SELECT "John" || " " || "Paul";

If we run the same statement like this:

SELECT "John" || "Paul";

We can see that the space between the names has been removed.

How to concatenate the table’s columns in SQLite

We can concatenate the columns of the table by using the operators ” || “; to understand how to concatenate is used in the SQLite, we will create a table employees_name, having the columns of emp_is, emp_first_name, and emp_last_name using:

CREATE TABLE employees_name (emp_id INTEGER, emp_first_name TEXT, emp_last_name TEXT);

Now, insert some data in the employees_name table:

INSERT INTO employees_name VALUES (1, "John", "Paul"), (2,"Hannah", "Alexander");

To display the table, we use the statement:

SELECT * FROM employees_name;

To concatenate the first and last name together of the above table, use:

SELECT emp_id, emp_first_name || " " ||  emp_last_name FROM employees_name;

From the above output, we can see that both columns, emp_first_name, and emp_last_name are concatenated with each other having a space between them.


Concatenation is the process of joining two or more than two strings together in a chain or series, for this MySQL supports a built-in function, concat(), but it is not supported by SQLite. There are a lot of built-in functions, which are not supported by SQLite but there are alternatives through which we can obtain the same results. In this article, we have discussed how we can use the process of concatenation by using the concatenation operator ” || ” in SQLite.

About the author

Hammad Zahid

I'm an Engineering graduate and my passion for IT has brought me to Linux. Now here I'm learning and sharing my knowledge with the world.