Dynamic SELECT checking for records with apostrophe

Asked Jul 19 '17 at 15:26

Active Jul 19 '17 at 15:26

Viewed 37 times

If I use this simple select statement, it works:

SELECT * FROM @table
WHERE [A] LIKE '%&%' OR 
[A] LIKE '%''%'

But I get an error if I try:

DECLARE @SourceTable nvarchar(50) = '@table',
@ColumnName nvarchar(50) = 'A'

EXEC('SELECT * FROM '+ @SourceTable +
' WHERE ['+ @ColumnName + '] LIKE ''%&%'' OR ['+
@ColumnName + '] LIKE ''%''%''')

I get this error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '%'.

asked Jul 19 '17 at 15:26

Christa

4

Put 4 single quotes between the % signs. – Tab Alleman Jul 19 '17 at 15:29
@TabAlleman Correct. You should have posted this as the answer. – Eli Jul 19 '17 at 15:31
@Christa you doubled the apostrophes outside of the percent signs, though you forgot the do the same between the percent signs - hence the comment by Tab – Eli Jul 19 '17 at 15:32
@TabAlleman Thanks! I thought I tried it before, but I guess I didn't! – Christa Jul 19 '17 at 15:32
@Eli true, but I wanted to check for duplicates first. – Tab Alleman Jul 19 '17 at 15:32

Dynamic SELECT checking for records with apostrophe

0 Answers0