Do you need to escape special characters or escape a single quote in SQL? Learn how to do that in this article.
What Is Escaping?
When you are working with text values in SQL, you use single quote characters.
This could be when you’re selecting them:
SELECT 'Database Star';Or, when you’re inserting them:
INSERT INTO customer (id, customer_name)
VALUES (45, 'ABC Company');The single quote character indicates the start and end position of your string.
But what if you want to have a single quote as part of your string?
For example:
SELECT 'O'Reilly';This would cause an issue because there are three single quotes. The database doesn’t know where your string should end.
So, to allow values within single quotes (and some other special characters) to be used within a string, you need to “escape” them.
Escaping a character is where you say to the database, “Hey, this character here is part of my string, don’t treat it as a special character like you normally would”.
There are a few SQL escape single quote methods that I’ll cover in this article.
If you want to get even more tips and references for using SQL, get the SQL Cheat Sheets here:
Use Two Single Quotes For Every One Quote To Display
Vendors: Oracle, SQL Server, MySQL, PostgreSQL.
The simplest method to escape single quotes in SQL is to use two single quotes. For example, if you wanted to show the value O’Reilly, you would use two quotes in the middle instead of one.
The single quote is the escape character in Oracle, SQL Server, MySQL, and PostgreSQL.
SELECT 'O''Reilly' AS quoted_string;| QUOTED_STRING |
| O’Reilly |
If you want to use more than one in a string, you can.
SELECT 'Let''s go to that ''place''' AS quoted_string;| QUOTED_STRING |
| Let’s go to that ‘place’ |
Use a Backslash Before the Quote
Vendors: MySQL, PostgreSQL
In MySQL, you can add a backslash before the quote to escape it.
For example:
SELECT 'O\'Reilly' AS quoted_string;| QUOTED_STRING |
| O’Reilly |
Use Literal Quoting
Vendors: Oracle
Another SQL escape single quote method you can use in Oracle is “literal quoting”.
This means you can put the letter “q” in front, followed by your escape character, then square brackets.
For example:
SELECT q'[O'Reilly]' AS quoted_string
FROM dual;| QUOTED_STRING |
| O’Reilly |
This means that any quotes inside the square brackets are not escaped. The output string appears exactly as you have entered it.
Another example:
SELECT q'[The 'end' of the day]' AS quoted_string
FROM dual;| QUOTED_STRING |
| The ‘end’ of the day |
You can still do this with multiple single quotes as well.
SELECT q'[This is an ''example'' of two single quotes]' AS quoted_string
FROM dual;| QUOTED_STRING |
| This is an ”example” of two single quotes |
Use the CHR Function
Vendors: Oracle, SQL Server
Another method is to use the CHR function.
The CHR function returns a character from the specified ASCII code.
CHR(39) returns a single quote, which can be concatenated to your string.
For example:
SELECT 'O' || CHR(39) || 'Reilly' AS quoted_string;| QUOTED_STRING |
| O’Reilly |
You can use this in multiple places too.
SELECT 'The ' || CHR(39) || 'end' || CHR(39) || ' of the day' AS quoted_string;| QUOTED_STRING |
| The ‘end’ of the day |
Escaping SQL Wildcard Characters
Vendors: Oracle
When you’re using the LIKE keyword, you supply wildcard characters.
The % symbol represents zero or more characters, and the _ symbol represents one character.
What if you wanted those characters to be a part of your string and not a wildcard character?
You can do that as part of the LIKE keyword, by specifying the ESCAPE keyword.
SELECT customer_name
FROM customer
WHERE customer_name LIKE '%\_%' ESCAPE '\';This query looks for all customer_name values that contain an underscore.
Normally, the underscore indicates a single character wildcard, but I have specified the ESCAPE ‘\’ keyword here. This means that the \ character is used as an escape character, which forces the _ to be used literally and not as a wildcard.
We can specify a different escape character if needed.
SELECT customer_name
FROM customer
WHERE customer_name LIKE '%/_%' ESCAPE '/';This performs the same query, just using a different escape character.
We can also escape a % symbol.
SELECT customer_name
FROM customer
WHERE customer_name LIKE '\%%' ESCAPE '\';This query finds all customer_name values that start with a % symbol.
So, that’s how you can escape single quotes in SQL. There are a few methods, so you can use whichever method you prefer.
If you want to get even more tips and references for using SQL, get the SQL Cheat Sheets here:
V_emp_nm = ‘D’SOUZA’ ;
INSERT INTO EMPMST (EMP_NM) VALUES V_EMP_NM)
HOW TO TO DO ABOVE IN ORACLE INSERT
I think you have a syntax error. Could you try this?
V_EMP_NM = ‘D’SOUZA’;
INSERT INTO EMPMST (EMP_NM) VALUES (:V_EMP_NM);
V_emp_nm = ‘D’SOUZA’ ;
change to
V_emp_nm = ‘D’’SOUZA’ ;
INSERT INTO EMPMST (EMP_NM) VALUES (V_emp_nm )
How can I write select where V_emp_nm = ‘D’SOUZA’ ;
Can you post an article on bind variables?
What does the below statement actually mean?
INSERT INTO MyTable (ID) VALUES (:myId)
When You execute the query, You will be asked about the value of the “myid” variable on time
Quotes in strings are the pits, no question about that. I have taken a different approach and written a function to assist. The approach is basically to parse the string, and when single quotes are found to double them.
for example:
declare
l_string varchar2(32) := ‘O”Reilly’; — l_string looks like “O’Reilly” between the double quotes
begin
select quotes(l_string) from dual; — the function doubles the quote
end;
I use this approach a lot when I am putting together execute immediate sql statements.
I also added a second parameter that allows this parameter passed to surround the string. In the case of l_string, it would need to be a chr(39).
Here’s the code for the function. I do use the apex_string package to parse the string. Here’s an example, unless I made a typo typing it in, it should work. (I work in an Apex enabled environment. I can only assume the package is available in a non-Apex environment.)
create function quotes(p_string in varchar2, p_option in varchar2 default null) return varchar2
as
var1 apex_t_varchar2;
var2 varchar2(32000); — technically this should be a clob but I have never had the need for it
begin
if p_string is null then return null; end if;
var1 := apex_string.split(p_string,null); — creates an addressable array/table of characters
for i in 1..var1.count
loop
if var1(i) = chr(39) then
var2 := var2||var1(i); — when a quote is found, just double it
end if;
var2 := var2||var1(i); — copy the string over from the array to the character string
end loop;
return p_option||var2||p_options; — surround with quotes as needed
end quotes;
Hope this helps.
Awesome! Glad you’ve got a solution that works for you, and thanks for posting it here so others can benefit from it.
Thanks a lot! That helped!
How to write a query to display the string without using apostrope
If you want to remove the apostrophe you can use a SUBSTITUTE or REPLACE function depending on your database. You substitute the apostrophe for no character, and it should be removed.
Is that what you are after?
Yes, I tried below options in SQL but it doesn’t work, apostrophe remains, I’m trying to replace “it’s” with “its”
replace(col1, ‘\”’, ”)
replace(col1, ””, ”)
I also tried same with translate() and regexp_replace(), it still doesn’t work
Very Help full chr()
Hello. I have the following instruction in VBA in an Excel macro to upload info to the employees table.
It works very well if the emp.Name doesn’t have any ‘.
If so, MySql shows an error.
Any advise to fix this code?
Thanks
sSql = “Insert into employees (Name,Username,Active,Access,Movement,ChangeDate)” & _
” Values (‘” & emp.Name & “‘,'” & emp.Username & “‘,” & emp.Active & “,'” & _
emp.Access & “‘,'” & emp.Movement & “‘,'” & emp.ChangeDate & “‘);”
Thanks Ben,
useful stuff…. good work.
Just a note to
“Use the CHR Function
Vendors: Oracle, SQL Server”
Your example
SELECT ‘O’ || CHR(39) || ‘Reilly’ AS quoted_string;
… will NOT work with M$ SQL-Server .
SQL Server provides function CHAR(), not CHR() and does not support “||” as string concat operator like all other serious database providers on this planet.
SQL server uses “+” for concatination.
(no, I don’t no why they decided to implement it this way, and 5+ days the week I forget this weird behaviour and have “invalid syntax errors” on MSSQL.)
Correct syntax MSSQL;
SELECT ‘O”Reilly’ AS quoted_string union all
SELECT ‘O’ + CHAR(39) + ‘Reilly’ AS quoted_string ;
Result:
quoted_string
————-
O’Reilly
O’Reilly
TKS