In my earlier post on “How to Escape a Single Quote in SQL Server? – Interview Question of the Week #192 I showed you a method of escaping a single quote.
In this post, I will show you another method to produce the same result
Let us consider the same example
select ‘This is SQL Authority”s author Pinal Dave’ as result
The result of the above select statement is
result
—————————————–
This is SQL Authority’s author Pinal Dave
As shown in the select statement, you need to use two single quotes to produce a single quote in the result
Do you know there exists another method too to do the same thing?
You can make use of char function to append a single quote in the text
SELECT 'This is SQL Authority'+char(39)+'s author Pinal Dave' AS result
The result of the above select statement is
result
—————————————–
This is SQL Authority’s author Pinal Dave
char(39) will produce a single quote as a result which is appended in the select statement at the correct place.
You can use whichever you think is easier for you. However, I would love to know which method do you personally use for when you have to escape single quotes. If you post it in the comments section, I will be happy to post it here with due credits.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
I would love to use Char(39) because it gives me indication on how to print characters which are not even present on my keyboard. And also it clearly tell me visually that it’s printing of single quote and not string termination.
Definitely 2 single quotes as by this way there is extra overhead of string concatenation
Two single quotes.
SELECT ‘This is SQL Authority’+char(39)+char(39)+’s author Pinal Dave’ AS result
Maybe it is not optimal in terms of performance, I use replaces when code becomes unreadable – in nested code for example, so I can get one or two level extra :)
select replace(replace(‘This is single quote: `, this is double quote: ~’, ‘`’,””), ‘~’, ‘”‘)
That will give you literally two single quotes in the result string.