You can translate the question and the replies:

String Processing to array to last item in 1 swoop

I have a data source with multiple columns being a uri. I need to process it to get the last "item". We can simply create one for the sake of this question & try it out using the VQL shell. select split('/', 'http://server:9090/denodo-restfulws/tutorial/views/') as views this is ok, I can see that the views column is now an [Array]... I want the last item, which is item 6, or [5] of the array. but I can't do that in the same view/query. I had turn it into a sub-query for it to be able to retrieve the inside of the split. so the query goes as select views[5].string from ( select split('/', 'http://server:9090/denodo-restfulws/tutorial/views/') as views ) as v5 also, since the [Array]... is being just a column, count(views) will always return 1. So there are 2 questions here 1. is it possible to do something like select split('/', 'http://server:9090/denodo-restfulws/tutorial/views/')[5].string as views_v5 because currently, I'm getting "Syntax error: Exception parsing query near '['" even putting a bracket around it like select (split('/', 'http://server:9090/denodo-restfulws/tutorial/views/'))[5].string as views_v5 didn't work. 2. So putting a hardcoded no. like 5 in the array to retrieve it isn't the most elegant way of doing things but it certainly is the easiest (it's not like the uri changes all the time) but is there a better way rather than flattening it to run a count(*) and then doing it (flatten, count, retrieve) for the next uri columns. I have 3 uri columns & that means a lot of flattening, etc just to turn 'http://server:9090/denodo-restfulws/tutorial/views/' into 'view'. Is there a quicker way, just 1 swoop / one-liner in the output tab to split & retrieve the last split "item"? Thanks.
22-06-2015 01:13:59 -0400

2 Answers

Hello, If you're looking to get always the last 'item', I think the optimal (and easiest) way to go would be to use regular expressions with the Denodo function 'regexp'. The function regexp( 'http://server:9090/denodo-restfulws/tutorial/views/', '.*\\/(.*)\\/', '$1') returns the last item between slashes, 'views'. Hope this helps.
Denodo Team
29-06-2015 12:28:54 -0400
Yes, regex, shd have thought of that. excellent! cheers!
 Edited on: 28-07-2017 05:35:22 -0400
You must sign in to add an answer. If you do not have an account, you can register here