8 Replies Latest reply on Mar 29, 2013 9:54 AM by Steven Hawkins

    regex like UDF

    virtualdatabase Apprentice

      Just wondering if anyone has implemented a regex like UDF?

       

      I have a need to perform a like within a case statement to derive a new column and the basic "LIKE" functionality isn't going to work

       

      thanks!

        • 1. Re: regex like UDF
          Steven Hawkins Master

          If you are on Teiid 7.5 or later you can use like_regex or similar to https://issues.jboss.org/browse/TEIID-1602

           

          See also https://docs.jboss.org/author/display/TEIID/Criteria

          • 2. Re: regex like UDF
            virtualdatabase Apprentice

            thanks for the quick answer...

            I'm trying this for my criteria:

            where description like_regex ('^rad[0-9]$')

            but get an oracle error (underlying source):  ORA-00920: invalid relational operator

             

            the pushed-down translation is:

             

            AND g_0.SOURCE_STRING REGEXP_LIKE '^rad[0-9]$'

             

            any thoughts?

             

            do I need to add a = 'true' or something??

            • 3. Re: regex like UDF
              Steven Hawkins Master

              Yes, that is not the correct SQL for Oracle.  Can you open an issue?  A temporary workaround would be to create a custom OracleExecutionFactory that returns false for supportsLikeRegex.

               

              Steve

              1 of 1 people found this helpful
              • 4. Re: regex like UDF
                virtualdatabase Apprentice

                Sure, I'll open an issue.

                 

                In the meantime I'm attempting to use the function on a #TEMP table  (thus no push)

                 

                I use the following l:

                 

                case

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*0$') then 0

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*1$') then 1

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*2$') then 2

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*3$') then 3

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*4$') then 4

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*5$') then 5

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*6$') then 6

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*7$') then 7

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*8$') then 8

                when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*9$') then 9 else null end as birad from #TEMP1

                 

                I always get null

                 

                I've tested the regex by using grep:

                 

                grep '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*6$' *.txt

                 

                changing the '6' for '1' and having a few files to test against... all work fine..

                 

                Any thoughts or another issue to log?

                • 5. Re: regex like UDF
                  Steven Hawkins Master

                  > Any thoughts or another issue to log?

                   

                  That regex looks fine and works for me locally with a temp table against string values like 'aradmcat10'?  Do you have appropriate reportText values and what client are you sending the query through (you may want to check the command log to ensure that we're getting the sql appropriately).

                   

                  Steve

                  • 6. Re: regex like UDF
                    virtualdatabase Apprentice

                    I just realized a i can test quickly by building #TEMP1 in a way that you  can as well..

                     

                    insert into #TEMP1 (

                    select  'BI-RADS CATEGORY 6' as reportText

                    union

                    select  'BI-RADS CATEGORY 5'as reportText

                    union  

                    select 'BI-RADIOLOGYS 5:00'as reportText

                    union

                    select  'birad categegoy6' as reportText )

                     

                    then the same query from earlier

                     

                    The only one that returns is one of the '6' values ??

                    • 7. Re: regex like UDF
                      virtualdatabase Apprentice

                      Never mind for now.. must be something else as this test case now works :

                       

                      select reporttext, case

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*0.*$') then 0

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*1.*$') then 1

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*2.*$') then 2

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*3.*$') then 3

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*4.*$') then 4

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*5.*$') then 5

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*6.*$') then 6

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*7.*$') then 7

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*8.*$') then 8

                      when reportText like_regex ( '^.*[Rr][Aa][Dd].*[Cc][Aa][Tt].*9.*$') then 9 else null end as birad from #TEMP1

                       

                      using the same #TEMP I built above

                       

                      not sure what's going on with the 'real' data other than it's a clob insted of  a simple string

                      • 8. Re: regex like UDF
                        Steven Hawkins Master

                        The oracle pushdown handling was corrected with https://issues.jboss.org/browse/TEIID-2452