Nth Occurence
Looking for an Oracle equivalent of substr/instr extracting the nth occurrence of something.
I know the Substr() function in Datameer. Need a function to location the Nth occurrence of in datameer now.
example:
1234_4321_abc_cda_aa
Want results between underscores:
1234
4321
abc
cda
aa
-
Official comment
Thanks for the question BJ. This sounds like the TOKENIZE function.
With the TOKENIZE function, you can extract a specific element from the original string. Alternatively, you can create a list of all elements using the separator value.
If you'd prefer single rows for each result instead of a one-cell LIST type object, you can wrap the EXPAND function around the resulting LIST type.
Comment actions -
You can use the LISTELEMENT function in order to be able to extract values out of a list into a column. There is not a function to expand all the lists fully into separate columns, each list element that you want would need to be extracted into a dedicated column.
This is trivial for the example case which you have displayed that contains 5 records in the list.
Please sign in to leave a comment.
Comments
3 comments