How to insert a value that contains an apostrophe (single quote)?

Escaping single quotes

SQL uses single quotes to recognize string literals. Think of it as a secret language understood only by SQL, where the single quote is a VIP pass. Unfortunately, this makes the apostrophe in O'Brien a bit of a party crasher.

The double quote VIP pass

So how do you let O'Brien in without any drama? Give him a duplicate pass—double the single quote:

UPDATE customers SET name = 'O''Brien' WHERE id = 1;
-- SQL VIP party: "Oh, O'Brien, the last time you came, you made us confuse our grammar!" 


Parameterized query: The conga line

Parameterized queries allow every character, special or not, to join the party without the need for a VIP pass. They're considered more secure too and prevent gatecrashers like SQL injection:

query = "INSERT INTO customers (name) VALUES (?);"
params = ("O'Neil",)
cursor.execute(query, params)


ORM: The bouncer

Object-Relational Mapping (ORM) tools handle all the hassle of VIP passes and let in everyone safely:

new_customer = Customers(name="O'Neil")
session.add(new_customer)
session.commit()
-- ORM: "Relax O'Neil, no VIP drama in my club."


Dealing with complex situations

For raw SQL handling, understanding the nuances of special characters is critical.

REPLACE function: The Makeup artist

Replace each ' with '' using REPLACE function in SQL. It's like a makeup artist making everyone look 'double' good at the party:

INSERT INTO reviews (text) VALUES (REPLACE('Today''s performance was O''Neil''s best!', '''', ''''''));
-- REPLACE function: "I can even make the most awkward sentences look good!"


SQL Server's QUOTENAME: The Master of disguise

SQL Server gives us QUOTENAME function, which takes care of quoting identifiers:

SELECT QUOTENAME('O''Neil');
-- QUOTENAME: "We all wear masks, metaphorically speaking!"


Table variable: Safe practice zone

When you want a practice arena that doesn’t persist beyond the current session, use a table variable:

DECLARE @customers TABLE (name NVARCHAR(100));
INSERT INTO @customers (name) VALUES (N'O''Neill');
-- SQL: Wake up, O'Neill! This was just a rehearsal, the real party is over there.


Coding with standards

Doubling apostrophes looks like a neat little trick, but following coding best practices is crucial.

Safety first

Use ORM tools or parameterized queries for security against SQL injections and hassle-free coding.

Valuable symbols

Your code deserves to express maximum meaning, the same way you do. So, choose your symbols with care.