You can translate the question and the replies:

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

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.
29-09-2016 16:00:31 -0400

4 Answers

Hi, What do you mean you cannot acces the opp_array elements? Take into account that the split function generates an array and the element field within the array will have a name that you need to specify. When using split the name of this field will be "string". So, for instance, the following query works for me: ``` select my_string_array[0].string from (select split('_','ABLMNC_154342_O_UW_UA-UCOU_SMC') as my_string_array from dual()) ``` and returns: ``` ABLMNC ``` Have you tried this?
Denodo Team
30-09-2016 04:35:27 -0400
Thanks for the help. This worked for me.
 Edited on: 28-07-2017 05:35:22 -0400
I tried this method: select my_string_array[0].string from (select split('_','ABLMNC_154342_O_UW_UA-UCOU_SMC') as my_string_array from dual()) With question mark as a separator, I need to add '\' in the front, then it works. But if I select more columns, then it's failed again, by showing no value there. Example: select column_A, column_B, column_C, column_D, my_string_array[1].string from (select split('\?','') as my_string_array, column_A, column_B, column_C, column_D from mydatabase) a
24-06-2022 08:47:34 -0400
Sorry, please ignore and delete my answer above. It's solved.
24-06-2022 09:23:14 -0400
You must sign in to add an answer. If you do not have an account, you can register here