Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

substitute for regexp_count

Marco
Padawan

i need to switch an oracle sql to exasol. the follwoing sub sql is part of a bigger sql. i get a PARAMETER during runtime which has a dynamic amount of sub parameters concatenated in it. divided by a comma.

PARAMETER = PARAMETER1,PARAMETER2,PARAMETER3

The sql i used on oracle to create a row for each sub parameter used some regular expressions. unfortunately the function regexp_count is not available on exasol :

select trim(regexp_substr('$$PARAMETER','[^,]+',1, level)) as wert
from dual
connect by level <= 2--regexp_count('$$PARAMETER',',')+1

So,  the count delivers a 3 for the example and returns the 3 rows for the 3 sub parameters.

Do you have an idea which function i could use instead of the REGEXP_COUNT() to get the commas counted in the string ? 

thank you

1 ACCEPTED SOLUTION

Accepted Solutions

mwellbro
Xpert

as a thought: replace everything that´s not a comma and use length(<replaced-string>) ?

View solution in original post

3 REPLIES 3

mwellbro
Xpert

as a thought: replace everything that´s not a comma and use length(<replaced-string>) ?

exa-Aleksandr
Team Exasol
Team Exasol

Interesting, replacement of everything that is not a comma seems to require a regexp, while the difference between original string length and after replacing commas (minding NULLs on the way everywhere) might work with plain REPLACE (performance).

Marco
Padawan

Thanks guys. Worked so far . Example Parameter substituted with example content):

select trim(regexp_substr('GYC_BASE;2021-11-10T01:00:00,GYC_BASE_VA;2021-11-10T01:00:00','[^,]+',1, level)) as wert
from dual
connect by level <= LENGTH(REGEXP_REPLACE('GYC_BASE;2021-11-10T01:00:00,GYC_BASE_VA;2021-11-10T01:00:00' , '\w|;|:|-'))+1