-
1. Re: regex like UDF
shawkins Mar 28, 2013 11:17 AM (in response to virtualdatabase)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 Mar 28, 2013 12:07 PM (in response to shawkins)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
shawkins Mar 28, 2013 12:19 PM (in response to virtualdatabase)1 of 1 people found this helpfulYes, 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
-
4. Re: regex like UDF
virtualdatabase Mar 28, 2013 4:00 PM (in response to shawkins)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
shawkins Mar 28, 2013 4:06 PM (in response to virtualdatabase)> 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 Mar 28, 2013 4:19 PM (in response to shawkins)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 Mar 28, 2013 4:35 PM (in response to virtualdatabase)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
shawkins Mar 29, 2013 9:54 AM (in response to virtualdatabase)The oracle pushdown handling was corrected with https://issues.jboss.org/browse/TEIID-2452