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 (DSouja);

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 = DSouja;
UPDATE testTab1 SET someText = DSouja WHERE sometext = DSouja;

Hope you find this article helpful.

Please subscribe for more interesting updates.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s