Single Quotes in String Values

The following code includes an example of building a string value which has a single quote character in the text and also searching a field for a single quote character.

declare @test varchar(1000)

declare @i int

IF OBJECT_ID('tempdb..#testtable') IS NOT NULL  

    DROP TABLE #testtable

create table #testtable

(val varchar(200)

)

-- Intentionally misspelled

set @i = 1

set @test =  'Had this been a real emergency, you''re keester would already by fried'

 

while @i < 20

begin

  insert #testtable

  select cast(@i as varchar(1000))

  set @i = @i + 1

end

insert #testtable

select @test

-- find the one record 

select val

from #testtable

where val like '%''%'