MySQL Single Quotes in Strings

We frequently deal with names/strings that contain apostrophes or single quotes. It can be difficult to manage them in the database. If we provide the name as is, it will not appear in the search, and it may damage the application code; even other DML actions like INSERT, UPDATE, and DELETE may require a tiny change.

Let’s look at how to handle apostrophes and single quotes in strings and names.

–Creating a table to hold single quote strings.
CREATE TABLE testTab1(
someText varchar(10)
);

–Inserting the values
INSERT INTO testTab1 values (‘D”Souja‘);

As you can see, we added one more single quote to the above name. As per the MySQL documentation a ' inside a string quoted with ' should be written as ''.

–Let’s search for the same string
SELECT * FROM testTab1 WHERE someText LIKE ‘%”%‘;

–Removing the single quote from the string and adding it again.
UPDATE testTab1 SET someText = ‘DSouja‘ WHERE sometext = ‘D”Souja‘;
UPDATE testTab1 SET someText = ‘D”Souja‘ WHERE sometext = ‘DSouja‘;