getting string from a substring

Database

getting string from a substring

Hi All,

 

I have a table with 2 columns as below

 

IDTEXTVAL
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.

 

Thanks

Vinay

2 REPLIES
Senior Apprentice

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 !!!!!!