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

brianfriedman
brianfriedman Domo Employee

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?

Comments

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

  • cmarkum
    cmarkum Contributor

    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

  • dark_knight
    dark_knight Domo Employee

    @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"
  • zcameron
    zcameron Domo Employee

    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.

  • Can someone suggest a solution? Thank you.

  • Starlord
    Starlord Domo Employee

    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"
  • Try using single quotes 2 times:

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

    Then should help.

  • Hi There

     

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

    CASE WHEN `Metric` = 'Don\'t Use' THEN ....
  • dark_knight
    dark_knight Domo Employee

    @user10221  Is there any additional context you can provide? There are a number of solutions listed in this post that have been successful for most users. Can you provide an example of the issue you are running in to? 

    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"