Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Single Quotation Marks in SQL

By Kenneth Fisher, (first published: 2013/01/03)

Possibly one of the most difficult parts of dynamic SQL is dealing with single quotation marks.  I'm guessing most DBAs at one point or another have seen the following:

REPLACE(@quotedvar, '''', '''''')

For those who haven't seen this, or don't really understand it, what the code is doing is replacing all of the single quotes (') with 2 single quotes ('').  I'll go into the why a little farther down.

Here are my are 2 rules when dealing with single quotes. 

  1. The outside 2 single quotes delimit the string.
  2. On the inside of the string you must have 2 single quotes for each single quote you are representing.

First let's break down the strings the REPLACE is using: '''' and ''''''. The first thing I'm going to do is to color the outside two quotes so that we see what we are working with a bit more clearly.

'''' and ''''''

Now we can see the inside quotes a bit more clearly.  Note again there are 2 single quotes for each single quote we want to represent.  If it helps, think of putting O'Neil into a string.  You would write @var = 'O''Neil'.  Then if you get rid of the letters you end up with @var = ''''.  Hopefully this also makes '''''' a little easier to understand.  When you look at it try to ignore the outside quotes and see the inside quotes in pairs.  So '''''' actually represents ''.

Why would we want to mess with this?  Since T-SQL uses 's to delimit strings there has to be a way to put a single quote inside of the string.  (I'm not going into QUOTED_IDENTIFIER here.  If you are curious look it up in BOL.)  For example the compiler is going to have a hard time understanding 'O'Neil'.  Is it the string O'Neil?  The string Neil with a mistaken 'O at the beginning?  Or the string O with a mistaken Neil' at the end.  It's very similar to the problem of extra commas in a comma delimited file.  The way this is handled is by using two single quotes.  We put 'O''Neil' and the compiler is happy, it understands that what you are trying to say is O'Neil. 

Sounds simple right?  So let's try it out.

DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)

SET @quotedvar = 'O''Neil'
SET @sql = 'PRINT ''' + @quotedvar + ''''

PRINT @sql

EXEC sp_executesql @sql

The output looks like this:

PRINT 'O'Neil'

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'Neil'.

Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark after the character string ''.

Now I hear someone muttering at the back of the class saying “I put in the two single quotes like you told me but it's still wrong!”

And they would be right.  We put 2 single quotes in each SET statement.  But note, when we printed the @sql statement we got “PRINT 'O'Neil'”.  Single quotes both before and after O'Neil just like we intended.  However, when we run it, we are back to 'O'Neil' again.  We stored 'O''Neil' into @quotedvar, why didn't it transfer correctly?  Let’s look.

SET @quotedvar = 'O''Neil'

Well first the quotes on the outside delimit the string so they are ignored when the value is stored into the variable.  That would be why the extra single quotes in the SET @sql statement.  ( SET @sql = 'PRINT''' + @quotedvar + '''' )  But remember, when the value was stored into the variable the two single quotes ('') were translated into a single quote (').  So now the variable has “O'Neil” in it.  What we need to be stored in @sql is “PRINT 'O''Neil'”. 

Now everyone go back to the top, I'll wait.

Everyone back?  Ok, here we go

DECLARE @quotedvar nvarchar(100)
DECLARE @sql nvarchar(1000)

SET @quotedvar = 'O''Neil'

SET @sql = 'PRINT ''' + REPLACE(@quotedvar,'''','''''') + ''''

PRINT @sql

EXEC sp_executesql @sql

Look familiar?  Now our output looks like this:

PRINT 'O''Neil'

O'Neil

Everyone follow?  Good.  Now for homework please fill in the following:

DECLARE @topsql nvarchar(200)

SET @topsql =
      'DECLARE @quotedvar nvarchar(100) ' + char(13) +
      'DECLARE @sql nvarchar(1000) ' + char(13) +
…..
…..
…..
…..
…..
      'PRINT @sql ' + char(13) +
      'EXEC sp_executesql @sql '

PRINT @topsql
PRINT '-------'

EXEC sp_executesql @topsql

If you look closely this piece of code takes the previous example prints it out and then and runs it dynamically.  In case you have never tried it before this would be similar to dynamically creating dynamic SQL.

As a clue the output should look like this:

DECLARE @quotedvar nvarchar(100)

DECLARE @sql nvarchar(1000)

SET @quotedvar = 'O''Neil'

SET @sql = 'PRINT ''' + REPLACE(@quotedvar,'''','''''') + ''''

PRINT @sql

EXEC sp_executesql @sql

-------

PRINT 'O''Neil'

O'Neil

I’ll put the answer in the comments next week!

Total article views: 21537 | Views in the last 30 days: 366
 
Related Articles
ARTICLE

Trouble printing out long VARCHAR(MAX) strings?

Ever try to print out a long VARCHAR(MAX)/NVARCHAR(MAX) string, only to find that it's truncated at ...

FORUM

Concatinate the String with in single row

Concatinate the String with in single row

SCRIPT

Convert a string list to TABLE with single CTE query

Converts a string list into a table using a single query, no variables and no helper functions.

ARTICLE

Printing in .NET

A bit of a break from the SQL Server side with this great new .NET class developed by new author Jer...

SCRIPT

convert string to ascii values

print every ascii value from a string

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones