Thanks again. That was my understanding about how to do it but I was not having success. But now you have confirmed doubling the single quote should work I will (steal your code) <s> and go back and try it again.


>>Now a followup question. This is is very useful and I am trying to figure out how to do this passing in the table and column names as variable
>>
>>I think I need to build the expression as a string and then execute it, as just subsituting @colname for CID in the code below doesn't work (just returns the value of @colname for all rows)
>>
>>Since the string contains single quotes, I tried escaping each single quote with another single quote. Tried using Double Quote around the string I'm building. Not sure how to go about this
>>
>>

>>DECLARE @colname VARCHAR(100) = 'cid'
>>DECLARE @tblname VARCHAR(100) = 'caccount_credits'
>>DECLARE @str VARCHAR(max) = ''
>>
>>SELECT @str = @str + 'SELECT CASE WHEN NOT (' + @colname + ' LIKE expression + '  -- and here is where I don't know what to put in so '%' renders correctly in the result
>>
>>????
>>
>

>
>

>DECLARE @colname VARCHAR(100) = 'cid'
>DECLARE @tblname VARCHAR(100) = 'caccount_credits'
>DECLARE @str VARCHAR(max) = ''
>
>SET @str = '
>SELECT CASE
>       WHEN NOT ('+@colname+' LIKE expression + ''%'' OR '+@colname+' LIKE ''{'' + expression + ''}%'') THEN '+@colname+' 
>       END
>FROM   '+@tblname+' T
>       CROSS APPLY (SELECT REPLACE(''00000000-0000-0000-0000-000000000000'', ''0'', ''[0-9a-fA-F]'')
>                                                                             COLLATE Latin1_General_BIN) C2(expression)
>WHERE  NOT ('+@colname+'  LIKE expression + ''%'' OR '+@colname+'  LIKE ''{'' + expression + ''}%'')'
>
>SELECT @str
>