Spotfire Case statement find text

Question

I must be drawing a blank but I need to write a case statement that searches the text and if "(2)" exists anywhere within the text then "two" else if "(3)" exists anywhere in the text then "three". The text values are varying lengths and appear in different parts of the string so I can't use a right() or left(). I tried using the 'case when [column] ~= "(2)" then "two" ......end' but it didn't work properly.

Answer 1

Here is an expression that should get you what you want:

case  
    when [Value]~=".*\\(2\\).*" then "Two" 
    when [Value]~=".*\\(3\\).*" then "Three"
end

Note that CASE statements are executed in order, and BREAK once a condition is met. Thus, as you see in the 4th row, if both (2) and (3) are present, it will result to true for "Two" and the "Three" check will never happen.