How to Insert a Value that Contains an Apostrophe in SQLite?

SQLite is a self-contained, serverless and transactional SQL database engine. It is used in many other applications that need a lightweight, embedded database. In this article, we are going to see how we can insert a value that contains an apostrophe in SQLite using various examples and so on

Understanding the Issue with Apostrophes

The most probable case with the need to use apostrophes is when handling the string types. Before understanding how to deal with apostrophes let's understand what string type means and how is it related to apostrophes.

Strings are a datatype of SQLite. There are various types of string available in SQLite some of them are as follows:

• CHARACTER(20)
  
• VARCHAR(255)
  
• VARYING CHARACTER(255)
  
• NCHAR(55)
  
• NATIVE CHARACTER(70)
  
• NVARCHAR(100)
  
• TEXT
  
• CLOB

Let us create a sample table to see how to insert string values.

CREATE TABLE test (
    name VARCHAR2(15),
    id INT
);

Now let us insert string values into it. To do that we will surround the values in quotes.

INSERT INTO test VALUES ('Aadarsh', 1);
INSERT INTO test VALUES ('Aayush', 2);
INSERT INTO test VALUES ('Dhruv', 3);

Output:

Now let us try to insert a value with an apostrophe in it.

INSERT INTO test VALUES ('Aaka'sh', 4);

The database throws an error:

Error: near line 12: near "sh": syntax error

The error does not directly mean it is related to the extra apostrophe in the string but it is caused by that. The database treats the value `’Aaka’sh’` as two separate words including one complete word `’Aaka’` and another value starting from `sh’` but not complete yet. Therefore, it points that there is some syntax error near `sh'`.

How to Insert Values with an Apostrophe

Let's understand How to insert a value that contains an apostrophe in SQLite with the below methods.

Setting Up Environment

Let us create a table that will have all the string types and insert values with apostrophes into it.

CREATE TABLE test_dq 
(
  character_column CHARACTER(20),
  varchar_column VARCHAR(255),
  varying_character_column VARYING CHARACTER(255),
  nchar_column NCHAR(55),
  native_character_column NATIVE CHARACTER(70),
  nvarchar_column NVARCHAR(100),
  text_column TEXT,
  clob_column CLOB
);

Method 1: Use Single Quotes Twice to Escape Single Quotes

When the string is enclosed in single quotes then single quotes have a special meaning to them. They are used to notify the starting and ending of the string. To escape the special meaning we can write the single quote twice consecutively. Let us see the following example.

INSERT INTO test_dq VALUES 
(
'''a''bc''', '''d''ef''', '''g''hi''', '''j''kl''', '''m''no''', '''p''qr''', '''s''tu''', '''v''wx'''
);

Output:

Explanation: We get the output after inserting into the Use Single Quotes Twice to Escape Single Quotes.

Method 2: Use Double Quotes to Escape Single Quotes

When the string is enclosed in double quotes the single quotes behave as character literals rather than having any special meaning attached to them. To escape the single quote we can just enclose the string in double quotes. Let us see the following example.

INSERT INTO test_dq VALUES 
(
"'a'bc'", "'d'ef'", "'g'hi'", "'j'kl'", "'m'no'", "p'qr'", "'s'tu'", "'v'wx'"
);

Output:

Explanation: We get the output after inserting into the test table using Double Quotes to Escape Single Quotes.

Method 3: Use CHAR(39)

We can use the ASCII value of `’` (39) to insert it into strings. This can be done by concatenating different strings and putting CHAR(39) in between them. In the following the concatenation is done using the `||` operator.

INSERT INTO test_dq VALUES (CHAR(39) || 'a' || CHAR(39) || 'bc' || CHAR(39), CHAR(39) || 'd' ||
 CHAR(39) || 'ef' || CHAR(39), CHAR(39) || 'g' || CHAR(39) || 'hi' || CHAR(39), CHAR(39) || 'j' || CHAR(39) || 'kl' ||
 CHAR(39), CHAR(39) || 'm' || CHAR(39) || 'no' || CHAR(39), CHAR(39) || 'p' || CHAR(39) || 'qr' || CHAR(39), CHAR(39) || 's' ||
 CHAR(39) || 'tu' || CHAR(39), CHAR(39) || 'v' || CHAR(39) || 'wx' || CHAR(39));

Output:

Explanation: We get the output after inserting into the test table using Use CHAR(39).

Conclusion

Overall we have seen that we can enclose strings in quotes in SQLite. However to put apostrophes into strings themselves we need to escape them or use some other mechanism. There are three ways of doing this the first being to write the single quotes twice consecutively. The second is to use string literals and the third is to use the CHAR function.