1 Reply Latest reply on Nov 12, 2018 9:55 AM by Ritesh Bisht

    Like operation in Tableau like SQL(a%,%a and others) .Need solution using Regex

    DHRITIMAN BANERJEE

      Hi Community,

       

      I want to do a Like operation in Tableau but in Tableau 10.2, Like operation is not present.

       

      1)I am taking a data set for "a% like operation in SQL"

       

      BC I-123456
      BC I - 123456
      BCI12345667
      BC-I-123456
      BC-1&123456
      BC&1&123456
      BC I- 1

       

      In the above data set , those string pattern which starts with "BC I-" will be treated as true and other will be treated as false. The underlined and bold ones is true according to my dataset.

       

      As per my logic, I have used this logic :

       

      "IF STARTSWITH(lower([Name1]),'BC I-') THEN 1

      ELSEIF STARTSWITH(lower([Name1]),'BC I-') AND  FINDNTH([Name1]," ",2)=6 THEN 1

      ELSE 0 END"

       

      But this above code have complexity , if there is any space,special characters,characters after 'BC I-' , then it is treated false.

      I have to use findnth function to take space but it is not feasible  to do like this for all space,special characters and characters

       

      Can you please share a regex pattern or any better methods . So if there any space,special characters,characters after 'BC I-' ,it will return true else false.

       

      2)How to do SQL operation like '%a' in Tableau

       

      3) How to do SQL operation like '%aora%' in Tableau which finds any values that have "or" in any position in Tableau.

       

      4)How to do SQL operation like '_r%' which find any values that have "r" in the second position in Tableau.

       

      5)How to do SQL operation like 'a_%_% which finds any values that start with "a" and are at least 3 characters in length in Tableau.

       

      6)How to do SQL operation like 'a%o' which finds any values that start with "a" and ends with "o" in Tableau.

       

      I have attached my Tableau workbook for your reference.

       

      Regards,

      Dhritiman