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

AlexC92

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

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

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 .

MarkSnodgrass

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

MarkSnodgrass

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

AlexC92

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!