Searching for object with Special Characters CASE STATEMENT

[Chamopak]

Hi

I do not know if this question has been answered already, but I need your help

I got a data set that I need to filter and do calculations on. Some of the Member names have special character ' in it

like JOHN'S ATLO'S  GO'ON'MAN etc.

How do I get this statement working with these Characters


CASE
When (caption([object]) in (
'Charles',
'Jack',
'JOHN'S'
)
)
THEN (12345)
WHEN
When (caption([object]) in (
' ATLO'S',
'GO'ON'MAN' ,
'Combawamba'
)
)
THEN (9876)
ELSE
(102938)
END

And yes it works without the names with special characters  :'(

[bus_pass_man]

Escape the ' with another '

CASE
When (caption([object]) in (
'Charles',
'Jack',
'JOHN''S'
)
)
THEN (12345)
WHEN
When (caption([object]) in (
' ATLO''S',
'GO''ON''MAN' ,
'Combawamba'
)
)
THEN (9876)
ELSE
(102938)
END

Also, do you deliberately have a space before the A in ' ATLO'S' ?