3 Replies Latest reply on Aug 14, 2019 9:50 AM by Joshua Milligan

    REGEXP_REPLACE is not filtering

    Nancy Ratwatte

      REGEXP_REPLACE([BeneficiaryName],"[#,%,&,--,!,,,[],|","")

       

      The above formula is not filtering some of the items that i need to exclude?  I created new column with this formula but its not filtering any ideas why?

       

        • 1. Re: REGEXP_REPLACE is not filtering
          Joshua Milligan

          Hi Nancy,

           

          The following characters in your expression are reserved in Regex and require a preceding \ to escape them :

          • -
          • ,
          • [
          • ]

           

          If you write your expression as:

          REGEXP_REPLACE("!","[#,%,&,\-\-,!,\,,\[\],|]","")

           

          you should see the results you expect.

           

          Hope that helps!

          Joshua

          • 2. Re: REGEXP_REPLACE is not filtering
            Nancy Ratwatte

            Hi Joshua,

             

            Its still not working

             

            • 3. Re: REGEXP_REPLACE is not filtering
              Joshua Milligan

              Hi Nancy,

               

              Here are a couple of thoughts:

               

              1. One thing I find very helpful as I write regular expressions is to use any one of a number of online regex testers so I can see what matches the expression in my data.  Regex Tester and Debugger Online - Javascript, PCRE, PHP is a good one, though there are quite a few others out there.  I usually copy and paste a few sample rows of data and paste them into the Test String and then start writing my Regular Expression:

               

              In the specific regular expression you had above, I believe the correct expression would be [#,%,\-\-,!,\\,\-\-\-\-\-\-,&]

              That escapes all the - and \ characters.

               

              2. One other thing you might consider is whether there are some built in features of Tableau Prep that would meet your goals without having to use regex.  For example, you might be able to use the Clean functions on the field to remove unwanted characters:

               

               

              Now, it may be that it doesn't quite meet your needs.  It may remove characters you want to keep or not replace with a space or other character you want to use.  And regex may be the best approach.  There may be other approaches.  It's very difficult to know without understanding your exact goals and if this response doesn't give you the specifics you need to solve your issue, it would be very helpful if you would give more sample data and explain exactly how you want the transformed data.

               

              Hope this helps!

              Joshua

              1 of 1 people found this helpful