Execute the following scripts in Query Editor to demonstrate various examples to deal with quote within quote issue.

USE tempdb;

-- Search names for single quotes - CHAR(39)

SELECT BusinessEntityID,

       FullName = FirstName + ' ' + LastName

INTO Person

FROM AdventureWorks2008.Person.Person

WHERE CHARINDEX( CHAR(39), LastName) > 0

  OR  CHARINDEX( CHAR(39),FirstName) > 0

ORDER BY FullName

GO

SELECT * FROM Person

GO

/* Result

BusinessEntityID        FullName

2376                    Claire O'Donnell

1551                    Michael O'Connell

827                     Reuben D'sa

78                      Reuben D'sa

1557                    Robert O'Hara

767                     Thierry D'Hers

12                      Thierry D'Hers

1553                    Tim O'Brien

1555                    Tina O'Dell

*/

-- Double up single quotation mark (apostrophe) within the quotes

-- SQL like

SELECT * FROM Person

WHERE FullName LIKE '%O''Brien'

GO

-- Result: 1553   Tim O'Brien

-- Use char(39) to replace single quotes in the text

-- SQL pattern match

SELECT * FROM Person

WHERE FullName LIKE '%O'+CHAR(39)+'Brien'

GO

-- Result: 1553   Tim O'Brien

-- This will fail

SELECT * FROM Person

WHERE FullName = 'Tim O'Brien'

GO

-- '

/*

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'Brien'.

Msg 105, Level 15, State 1, Line 2

Unclosed quotation mark after the character string ''.

*/

-- Double up apostrophe for proper filtering -- successful filtering

SELECT * FROM Person

WHERE FullName = 'Tim O''Brien'

GO

-- SQL char 39  - explicit string concatenation - successful filtering

DECLARE @FullName varchar(50) = 'Tim O'+CHAR(39)+'Brien'

SELECT * FROM Person

WHERE FullName = @FullName

GO

-- Execute cleanup action

DROP TABLE Person

GO
------------

------------

-- Multiple quotes within quotes

------------

/*

To make it sp_MSforeachdb input parameter

      1. Enclose query in single quotes

      2. Double up single quotes within the query

      3. Replace AdventureWorks2008 with ?

If 'AdventureWorks2008' Not In ('tempdb', 'master', 'model', 'msdb')

Begin

Use AdventureWorks2008;

Select DatabaseName=DB_Name(), ForEachDBName='AdventureWorks2008'

End;

*/

-- SQL for each db

exec sp_MSforeachdb 'If ''?'' Not In (''tempdb'', ''master'', ''model'', ''msdb'')

Begin

Use ?;

Select DatabaseName=DB_Name(), ForEachDBName=''?''

End ';

/* Partial results

DatabaseName                  ForEachDBName

ReportServer$MSSQL2008  ReportServer$MSSQL2008

*/

------------