How to escape apostrophe (') in MySQL?

MySQLMySQLi Database

We can escape apostrophe (‘) in MySQL in the following two ways −

  • We can use backslash.
  • We can use single quotes twice (double quoted)

Using backslash

Let us first create a table.

mysql> create table SingleQuotesDemo
  - > (
  - > id int,
  - > name varchar(100)
  - > );
Query OK, 0 rows affected (1.16 sec)

Following direct usage does not give the desired result for name “John’s”.

mysql> insert into SingleQuotesDemo values(1,'John's');
    '>

Let us now use backslash.

mysql> insert into SingleQuotesDemo values(1,'John\'s');
Query OK, 1 row affected (0.19 sec)

We will now display the records.

mysql> select *from SingleQuotesDemo;

Here is the output, that shows we have correctly implemented Backslash.

+------+--------+
| id   | name   |
+------+--------+
|    1 | John's |
+------+--------+
1 row in set (0.00 sec)

Using double quotes

The following is the syntax for implementing backslash using double quotes. We are inserting another record in the same table used above.

mysql> insert into SingleQuotesDemo values(2,'John''s');
Query OK, 1 row affected (0.18 sec)

Now, we will display the records.

mysql> select *from SingleQuotesDemo;

The following is the output.

+------+--------+
| id   | name   |
+------+--------+
|    1 | John's |
|    2 | John's |
+------+--------+
2 rows in set (0.00 sec)

In the above possible ways, we can escape apostrophe(‘).

raja
Published on 21-Nov-2018 06:34:36
Advertisements