Understanding Single quotes

Often I see that newbies struggle to understand how single quotes work in SQL Server. The problem happens when you try to add text data which has single quote as part of it to a table.

I have seen newbies worrying why the following doesn’t work

SELECT columns from mytable where col ='Lifco's'

When you specify a value which has single quote, you need to double it

SELECT columns from mytable where col ='Lifco''s'

The following may be helpful (Run and see the result)

SELECT '','''','''''','''''''',''''''''''

When you use a static sql and express a value in a single quote then first and last single quotes specify that the value is a string. Then within those single quotes every double single quotes represent a single single quote

When you use a Dynamic sql then first and last single quotes specify that it is a dynamic sql. Then within those single quotes every double single quotes specify that it is a string.Then within those single quotes every four single quotes represent a single single quote

Execute the following code and see the result

EXEC
(
'SELECT 
'''','''''''','''''''''''','''''''''''''''',
'''''''''''''''''''''
)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s