You can translate the question and the replies:

conversion of SQL function to Denodo

UPDATE: I got something to work with REGEXP. I couldn't figure out how to delete my question. No longer need help. I'm using this statement in Oracle SQL: case when instr(reverse(tx.tx_comment), ';', 1) <> 0 then reverse(trim(substr(reverse(tx.tx_comment), 1, instr(reverse(tx.tx_comment), ';', 1) - 1))) else tx.tx_comment I've read that Denodo does not have a reverse function. I've tried to recreate this statement with other suggestions but I am having issues. Any help is appreciated. Basically trying to pull a bunch of numbers out of a string. Ex 1: I just need the long number string 3118035350103557000000000 from this example Charge reposted per Charge Router message 393909634; 3118035350103557000000000 Ex 2: I just need the 3118011350103584000000000 from this example - by SQL statement doesn't even take off the characters after the number string I need. Charge reversed per Charge Router message 388393602 from Beaker; 3118011350103584000000000 HLH Panel (Del/Dup 11-20 genes)
08-03-2024 07:56:22 -0500

1 Answer

Hi, I'm glad you were able to find a solution! I was able to implement the functionality you described in your question. If you have a base view that has a column containing the text you described, you can create a new selection view over it. In the output tab when editing this new view, you can create a new field and enter the following field expression: `case WHEN (instr(<base view name>.<field name>, ';') > -1) THEN substring(regexp(<base view name>.<field name>, '.*(?=; \d)|(?<=; \d+\b).*', ''), 2) ELSE NULL END` The `regexp` function will get rid of everything before the semicolon if the semicolon is followed by a digit. It will also get rid of everything after the number that we are hoping to extract. So far our text looks something like: `; ####` The `substring` function will finish the job by getting rid of the semicolon and space character leaving us with the number we wanted to extract. For more information on Denodo's text processing functions, you can visit the [Text Functions]( section of the VQL Guide. Hope this helps!
Denodo Team
11-03-2024 15:32:06 -0400
You must sign in to add an answer. If you do not have an account, you can register here