How to have quotes in literals with SQL Server

You need to retrieve data that has contains quotes within literals. 

select 'Where''s the place?' as 'Single quote in text';
go 

Single quote in text
--------------------
Where's the place?

(1 row(s) affected)
  1. The quote is a reserved character to literal strings.
  2. To have a quote with a literal string, you need to QUOTE IT.

ie: Where''s the place?, twice a quote will produce a single quote in the result set. 

select '' as '2 Quotes';
go

Produces: 

2 Quotes
--------


(1 row(s) affected)
  • The row returned contains NOTHING, 0 byte.
  • This is different from NULL. 
select ' ' as '2 Quotes';
  • Returns a space, 1 byte. 
select '''' as '4 Quotes';
go

Produces: 

4 Quotes
--------
'

(1 row(s) affected)
  • 4 quotes, returns only 1 quote! 
select ''' as '3 Quotes';

Produces 

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '3'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ';
'.

Quotes ' are like Brackets (, every opening one must have a corresponding closing one.