getting string from a substring


getting string from a substring

Hi All,


I have a table with 2 columns as below


10Sending-CallStatus:SS|SurveyOffered:N|SessionId:*******02-2016269190139-40|T02AccountId:000********|Verified:Y|CallDate:2016-09-25 14:08:27.238|Receiving-Status:success
20Sending-CallStatus:XFER|SurveyOffered:N|SessionId::*******01-2016269175459-27|T02AccountId:000******|Verified:Z|CallDate:2016-09-25 12:58:01.209|Receiving-Status:success
30Sending-CallStatus:XFERVINAY|SurveyOffered:N|SessionId::*******01-2016269162755-15|T02AccountId:000******|Verified:YN|CallDate:2016-09-25 11:30:45.711|Receiving-Status:success

If you see under TEXTVAL column highlighted is "Verfied:Y" for ID 10. Similarly "Verified:Z" and "Verified:YN" for 20 and 30.


I am trying to extarct only the value as Y,N,YN.


When i do substrt i can get the begging position but i am unable to provide extarct till you find '|' after value "Verified:". I had query like below


SyntaxEditor Code Snippet

select substr(VARVALUE,INSTR(VARVALUE,'Verified:')+9, ??? )

 Here the end position is where i am not sure how to provide it.


Can you please help me here.




Junior Contributor

Re: getting string from a substring

You should switch to a regular expression:

RegExp_Substr(VARVALUE, '(?<=Verified:)(.+?)(?=\|)')

This looks for 'Verified:' and extracts the following characters until the next '|'.

Re: getting string from a substring

Worked as I expected. Thanks a lot Dnoeth !!!!!!