Is there any Regexp_count alternative in Impala?

user_11834668 0
Viewed 140

I am looking for Oracle's Regexp_count alternative in Impala. Is there any alternative or is there any way we can implement regexp_count in Impala?

impala oracle sql
1 Answers

Replace pattern with some character which is not in your pattern, say tilde '~', then remove everything that is not a tilde, then calculate length of a string. If initial string can contain tildes, remove them all before.

For example to calculate how many times pattern test occurs in the string ( also contains tildes ) 'test ~something ~test, one more test~':

length(--get length (the number of tildes)
              regexp_replace(
                             regexp_replace(
                                            regexp_replace('test ~something ~test, one more test~','~',''), --remove all tildes
                                            'test','~'), --replace pattern with tilde
                              '[^~]+','') --remove everything that is not tilde
              ) --calculate the number of tildes

This will return 3

user_27003440
Related