How do I escape single quotes/apostraphes in a Beast Mode?

Reply
Highlighted
White Belt

How do I escape single quotes/apostraphes in a Beast Mode?

Suppose I have Beast Mode the following syntax:

 

CASE

WHEN `lastName` = 'O'Sullivan'

THEN ...

 

The apostrophe in O'Sullivan breaks the Beast Mode. Is there a way to escape the character?

Highlighted
Yellow Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

Standard mySQL syntax does not work here, so I would be curious to know if there's a solution to this, also.

Highlighted
Blue Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

I tried a couple mySQL syntax with no luck. The only thing that I found was to do a LIKE operator.

 

CASE WHEN LASTNAME LIKE 'O%'  AND LASTNAME LIKE '%Sullivan' THEN ...

 

The statement above just states that the last name must start with O and end with Sullivan.

 

Hope this helps

Highlighted
Green Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

@brianfriedman I have submitted an bug ticket with development for this and requested a way to escape charicters in Beast Modes. I will update you on it's progress when I know more.

 

Ticket # 00474129

Thanks!

I work for Domo.

**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"
Highlighted
Black Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

I hit this same issue. 

 

 

Until the fixed gets pushed out, a possible work-around is

 

CASE WHEN LASTNAME LIKE 'O_Sullivan' THEN ...

 

The "_" is a wildcard for a single character only. This will fail if a character other than an apostrophe could be between the "O" and "S", but will probably work for most specifica cases.

Highlighted
Purple Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

To escape a single-quote in a Beast Mode equals "=" operation, use 2 single quotes instead of 1. For example:

 

CASE
WHEN `lastName` = 'O''Sullivan'
THEN ...

 

Starlord
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
White Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

Can someone suggest a solution? Thank you.

Highlighted
Purple Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

Hello user08600, please see my previous response. Two apostrophes in a row represents an escaped apostrophe. For example, 'O''Sullivan' is valid for specifying the name O'Sullivan with an escape on the apostrophe.

Starlord
**Say “Thanks” by clicking the “heart” in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
Yellow Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

Try using single quotes 2 times:

INSTR(`column name`,'DOMO''s')

Then should help.

Highlighted
White Belt

Re: How do I escape single quotes/apostraphes in a Beast Mode?

Hi There

 

after 5 years this doesn't seem to be resolve. Even escaping:

CASE WHEN `Metric` = 'Don\'t Use' THEN ....
Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.