SQL SERVER – How to Escape a Single Quote in SQL Server?

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.

SQL SERVER - How to Escape a Single Quote in SQL Server? escape

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)

SQL Scripts, SQL Server
Previous Post
SQL SERVER – The Older Way to Find Size of Index Using Deprecated sysindexes
Next Post
SQL SERVER – Optimize Key Lookup by Creating Index with Include Columns

Related Posts

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.

    Reply
  • Definitely 2 single quotes as by this way there is extra overhead of string concatenation

    Reply
  • Michael Poppers
    November 11, 2018 9:47 am

    Two single quotes.

    Reply
    • Ramkumar Sambandam
      March 9, 2019 8:34 pm

      SELECT ‘This is SQL Authority’+char(39)+char(39)+’s author Pinal Dave’ AS result

      Reply
  • 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: ~’, ‘`’,””), ‘~’, ‘”‘)

    Reply
  • That will give you literally two single quotes in the result string.

    Reply

Leave a Reply