To get the rows with invalid characters. 1

[manohars]

I have to declare a cursor to get list of rows, which don't contain the following characters in a column.

exec('DECLARE curData CURSOR for SELECT count(*) from ' + @TableName + ' where
convert(varchar(255), [' + @ColName + ']) like
''%[^\\-\\^_<>`~:{}\",#$%&!?;+0-9a-zA-Z@/*()\\.\\-\\b\\\\ ]%')

I can only declare it inside EXEC statement as I have to pass the table name dynamically. But when I execute the statement above, I get the following error message,

"Unclosed quotation mark before the character string '%[^\\-\\^_<>`~:{}\",#$%&!?;+0-9a-zA-Z@/*()\\.\\-\\b\\\\ ]%'"

What could be the reason.

Also, one of the characters is hyphen, but this is not checked. Can anyone help why is it so?

Thanks in Advance,
Manohar

 

[gradley]

Because you are using a single quoted string inside a single quoted string, SQL is getting confused. Try using 2 single quotes for the inner quoted string

Ex:

exec('DECLARE curData CURSOR for SELECT count(*) from ' + @TableName + ' where 
convert(varchar(255), ['' + @ColName + '']) like 
''%[^\\-\\^_<>`~:{}\",#$%&!?;+0-9a-zA-Z@/*()\\.\\-\\b\\\\ ]%')

 

[manohars]

If you see the code, that's what I did. I put 2 single quotes in the beginning of the sql char list.

Manohar

 

[gradley]

I see that! My 2 single quotes suggestion was in the wrong place. I notice that you have the 2 single quotes at the beginning of the LIKE clause but not at the end.

Try this:

exec('DECLARE curData CURSOR for SELECT count(*) from ' + @TableName + ' where 
convert(varchar(255), [' + @ColName + ']) like 
''%[^\\-\\^_<>`~:{}\",#$%&!?;+0-9a-zA-Z@/*()\\.\\-\\b\\\\ ]%''')

I sometimes use a SET to variable statement to test out concatinated strings. I find it easier to work with

Ex:

declare @varString varchar(500)

SET @varString = 'DECLARE curData CURSOR for SELECT count(*) from ' + @TableName + ' where 
convert(varchar(255), [' + @ColName + ']) like 
''%[^\\-\\^_<>`~:{}\",#$%&!?;+0-9a-zA-Z@/*()\\.\\-\\b\\\\ ]%'''

Print @varString

I hope this helps!