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
------------
-- ESCAPE character in LIKE string
------------
DECLARE @Test TABLE ( Col1 varchar(255) );
INSERT @Test VALUES ('General discount is 20-35% off'),
('Weekly discount is .08-.14 off');
SELECT Col1 FROM @Test WHERE Col1 LIKE '%20-35!% off%' ESCAPE '!';
GO
-- General discount is 20-35% off
------------
------------
-- 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
*/
------------
|
