[SQL] How to escape single quotes in the words of Pinal Dave

I will answer this SQL riddle in the words of Pinal Dave:

Jr. Developer asked me other day how to escape single quote?

User can escape single quote using two single quotes (NOT double quote).

In case you need it (I doubt it) he also provides a great example.

The man is a SQL master, and if you're a Jr.Developer (I am not but when it comes to SQL I am probably less than that) you gotta follow him on twitter.  

Another way of injecting single quotes using CHAR(39):

DECLARE @IanQuigley as varchar(MAX)
SET @IanQuigley = 'Thanks, I don' + CHAR(39) + 't know much about SQL'

I did my worst - as usual.