SQLite | SQLite Functions | Enclosing a String in Single Quotes (quote Function)
The quote function returns a string enclosed in single quotes ('). This article explains how to use the function.
Using the quote Function
The quote function encloses a string in single quotes ('). Its syntax is as follows.
quote(value)
When the argument is a string, the function returns it in the form 'string', enclosed in single quotes. Numeric values are returned unchanged. BLOB data is returned in the form X'data'. Use the quote function when you need a value represented in a form suitable for an SQL statement.
If the supplied string contains a single quote ('), the function escapes it by adding another single quote.
When a column name is supplied, the function quotes each value stored in that column.
–
Let’s try an example. First, create the following table.
create table datadb (id, data);
sqlite> create table datadb (id, data);
sqlite>
Add the following data with INSERT statements.
insert into datadb values (1, 'Melon');
insert into datadb values (2, 18);
insert into datadb values (3, 3.52);
insert into datadb values (4, 'I''am a student');
sqlite> insert into datadb values (1, 'Melon');
sqlite> insert into datadb values (2, 18);
sqlite> insert into datadb values (3, 3.52);
sqlite> insert into datadb values (4, 'I''am a student');
sqlite>
Use the quote function to return the values stored in the data column in quoted form.
select id, data, quote (data) from datadb;
sqlite> .mode column
sqlite> .header on
sqlite> .width 10 10 20
sqlite>
sqlite> select id, data, quote (data) from datadb;
id data quote (data)
---------- ---------- --------------------
1 Melon 'Melon'
2 18 18
3 3.52 3.52
4 I'am a stu 'I''am a student'
sqlite>
Numeric values are displayed unchanged, while strings are returned enclosed in single quotes.