How to do a contains in a filter looking for ' (in O'Sullivan)

[cognosguru]

Hi folks,

I need to do a report finding all employees whose name contains a special character.  I can do contains '&' or contains '@' with no issues.  Where I get a syntax error is when I am trying to find all employees with an apostrophe (O'Sullivan, etc.).

Any suggestions?

Thanks!

[adam_mc]

Have you tried adding a second quote?
Something like contains ''''

Not sure, but I think it is something along these lines if I remember correctly.

Hope this helps,
Adam.

[dougp]

Code Select Expand

[DataItem] contains '(O''Sullivan, etc.)'

[cognosguru]

So this is fun and here's how I solved it:

I tried to create a simple filter not using Advanced and it worked by seemingly having Name contains '
When I do the same in advanced it fails so I examined the SQL and got this, tested it in advanced and it works:

[Employee Name (Last Suffix, First MI)] like '%''%'

Wow.

[dougp]

I think that's what adam_mc was saying:

Code Select Expand

[DataItem] contains ''''

[cognosguru]

Doug and Adam yes '''' works also.  I am still trying to understand mechanics of it instead of ''' because it seems to find anything and then something else and the ' is just whatever is in the middle?