Escape Character in SQL Server
Solution 1
To escape '
you simly need to put another before: ''
As the second answer shows it's possible to escape single quote like this:
select 'it''s escaped'
result will be
it's escaped
If you're concatenating SQL into a VARCHAR to execute (i.e. dynamic SQL), then I'd recommend parameterising the SQL. This has the benefit of helping guard against SQL injection plus means you don't have to worry about escaping quotes like this (which you do by doubling up the quotes).
e.g. instead of doing
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = ''AAA'''
EXECUTE(@SQL)
try this:
DECLARE @SQL NVARCHAR(1000)
SET @SQL = 'SELECT * FROM MyTable WHERE Field1 = @Field1'
EXECUTE sp_executesql @SQL, N'@Field1 VARCHAR(10)', 'AAA'
Solution 2
You can escape quotation like this:
select 'it''s escaped'
result will be
it's escaped
Solution 3
You can define your escape character, but you can only use it with a LIKE
clause.
Example:
SELECT columns FROM table
WHERE column LIKE '%\%%' ESCAPE '\'
Here it will search for %
in whole string and this is how one can use ESCAPE
identifier in SQL Server
.
Solution 4
You need to just replace '
with ''
inside your string
SELECT colA, colB, colC
FROM tableD
WHERE colA = 'John''s Mobile'
You can also use REPLACE(@name, '''', '''''')
if generating the SQL dynamically
If you want to escape inside a like statement then you need to use the ESCAPE syntax
It's also worth mentioning that you're leaving yourself open to SQL injection attacks if you don't consider it. More info at Google or: http://it.toolbox.com/wiki/index.php/How_do_I_escape_single_quotes_in_SQL_queries%3F
Solution 5
Escaping quotes in MSSQL is done by a double quote, so a ''
or a ""
will produce one escaped '
and "
, respectively.
esquare
Updated on February 20, 2022Comments
-
esquare about 2 years
I want to use quotation with escape character. How can I do to avoid the following error when one has a special character?
Unclosed quotation mark after the character string.
-
Seph over 9 yearsand yet answers by dugokontov or RichardPianka don't have any similar -1?
-
Seph over 8 years@MichaelMunsey try it for yourself:
select '
returns the errorUnclosed quotation mark after the character string ''
. Nowhere in my answer do I use"
only two'
, not sure why mine is the only answer with down votes. -
Peter Moore about 6 yearsWhy is this the accepted answer? It doesn't answer the question.
-
AdaTheDev about 6 years@PeterMoore Either the OP would have used the 1st part of my answer (doubling up the quotes, as per other answers below), or would have used the preferred approach I recommended for building up a SQL query in a string variable - to use parameterized SQL. Either way, both are answers to the question
-
Jamie Marshall almost 5 yearsThis is incorrect. Brackets work on illegal characters in field, table, or schema names.
-
Ben almost 5 yearsYeah, you right, its for the object names, not string contents. I must read the question wrong.
-
Tony over 4 yearsIt doesn't answer the question. Sometimes user need ODBC connection which means you can only use pure SQL.
-
Tony over 4 yearsThis should be the answer.
-
Revious about 4 yearsEdited answer to more it more clear and better fit the question