Search for keyword (from text input variable) in a phrase

AlexC92
AlexC92 Member
in Beast Mode

Hi all,

I am trying to find a way in Beast mode in which a user can input a text(keyword) in a box and then the formula would search the phrase to see if that keyword exists.

The problem I run into is that I need to add '% and %' respectively to a custom variable as part of LIKE. Can something like CHAR(37)& custom variable &CHAR(37) work?

Syntax would be something like:

Case

when column like '%`custom variable`%' then 'True'

else 'False'
End

syntax.png

the custom variable is created using " Textbox Input" control from "Add Variable" in the beast mode editor.

I would then use this calculated field in a dashboard to filter all the tables to only show me the results that contain this keyword.
The reason I am not hardcoding this keyword is because I need the ability for the user to search for many different case scenarios

textbox input.png

I was also thinking maybe I could use STR_CONTAINS(`Column`,custom variable) but it doesn't seem that this is recognized in Beast mode.

Is there anyway this could be done .

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You might try the INSTR() function as an alternative. You could do something like this:

    CASE WHEN INSTR(fieldname, variablename) >= 1 THEN 'True' ELSE 'False' END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    You might try the INSTR() function as an alternative. You could do something like this:

    CASE WHEN INSTR(fieldname, variablename) >= 1 THEN 'True' ELSE 'False' END

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • AlexC92
    AlexC92 Member

    Hi, thank you very much for the formula.
    while I was playing around I've done it like this.
    case
    when ITEM_DESC like concat('','%',Search Notes,'%','') then 'True'
    else 'false'
    END

    I've then compared the results and my variant was giving 5 more results. So I wrapped what u gave me into LOWER() and it stopped caring about case sensitivity.

    case
    when INSTR(lower(ITEM_DESC),lower(Search Notes))>= 1 then 'True'
    else 'false'
    END

    You have helped me a great deal, thank you very much!

Categories