Split function in Denodo does not work! (second way) Regex for complex string in Denodo

Question

I am basically trying to split the following string in Denodo and trying to create new columns in the database for each value.

my_string = ABLMNC_154342_O_UW_UA-UCOU_SMC

The problem i am facing is when i split the string, Denodo creates an array. But after that there is no way to access the a specific element using indexes. For example,

split('_',my_string) gives an

op_array[] = [ABLMNC,154342,O,UW,UA-UCOU,SMC]

but I cannot access op_array elements as op_array[0]. I have raised the issue in Denodo but no help.

So, as a way around i have started using regex. For now I am able to get the first string and the numbers.

my_string = ABLMNC_154342_O_UW_UA-UCOU_SMC

regex(my_string,'_[0-9]*_[A-Z]+_[A-Z]+_[A-Z]+-[A-Z]+_[A-Z]+','') = ABLMNC

regexp(my_string,'[A-Z_-]+',' ') = 154342

But I am not able to find other parts of the string. I would be really grateful if someone could help in any of these cases.

Thanks Wiktor. But, that gives me a string like this ABLMNC154342OUWUA-UCOUSMC. So this doesn't actually solve the problem. I am extracting it from a column and then trying to split it into multiple columns. — ashubhargave, Sep 28 '16 at 22:05

score 2 · Accepted Answer · answered Sep 30 '16 at 07:43

To get a value inside an array use (name of the field)[position of the array].<name of the field in the register>

For example:

SELECT (field1_array)[0].string AS f1, (field1_array)[1].string AS f2, 
       (field1_array)[2].string as f3, (field1_array)[3].string AS f4, 
       (field1_array)[4].string AS f5, (field1_array)[5].string as f6

FROM (

  SELECT split('_', field1) AS field1_array FROM (

    -- Subquery with that projects the value you mention. This would usually come from a regular view
    SELECT 'ABLMNC_154342_O_UW_UA-UCOU_SMC' AS field1 FROM dual()
  )
);

This query returns 6 columns with the values: ABLMNC, 154342, O, UW, UA-UCOU, SMC

Of course, this solution only works when the number of fields in the array is always the same.

Thanks a lot for the help MonteCarlo. – ashubhargave Sep 30 '16 at 16:56 — ashubhargave, Sep 30 '16 at 16:56

Split function in Denodo does not work! (second way) Regex for complex string in Denodo

1 Answers1