Nth Occurence

Follow

BJ Erickson

• September 20, 2016 18:21

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

 

0

• Official comment

  

  Joel Stewart

  • September 20, 2016 18:55

  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 Permalink
• 

  BJ Erickson

  • September 23, 2016 13:51

  Thanks!

   

  Is there a way to put each value in it's own column? 

  Example:

  Col1                                   Col2   Col3   Col4   Col5    Col6

  1234_4321_abc_cda_aa      1234   4321   abc    cda    aa

  0

  Comment actions Permalink
• 

  Joel Stewart

  • September 27, 2016 13:44

  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.

  0

  Comment actions Permalink

Please sign in to leave a comment.