search a string in text and get value between = and delimiter |

Database
Enthusiast

search a string in text and get value between = and delimiter |

Dear friends, we are storing below total record in single column of a table.

 

BILLINGVAL=null|ACCTNO=acb2582211dfd258|BILLINGACCSTATUS=ACQUIRED|BillCycle=22|BillFrequency=weekly|BillStartDate=14/12/2017|BillType=Consumer Detail|COLLECTIONS=N|CONTACTID=1-KQ0J6|CUSTOMERCODE=12457892|FinalStatusReason=|InvoiceArrIdNum=1112542453258

 

Requirement: search "customercode" string and pull the value that is after "=" and till delimiter | - "12457892 - so from CUSTOMERCODE=12457892, i want output of actual value 12457892, the same with another string ACCTNO - I want to search and get this value - acb2582211dfd258..

 

NOTE THAT these strings positions do change in the record and not following a fixed pattern. It can occur in any position. Please help at the earlier.

 

I'm trying with this below code already, just exploring any options that are less CPU intensive

SyntaxEditor Code Snippet

SELECT 
STRTOK(SUBSTR(col_name, POSITION('ACCTNO' IN data_val)+7,50),'|',1) AS acctno,STRTOK(SUBSTR(col_name, POSITION('CUSTOMERCODE' IN data_val)+13,50),'|',1) AS custcode
FROM TABLE_NAME 

 

regards, krish

3 REPLIES
Teradata Employee

Re: search a string in text and get value between = and delimiter |

You can try with regexp_substr, not sure about the CPU consommation :

with cte_data (data_val) as
(
select 'BILLINGVAL=null|ACCTNO=acb2582211dfd258|BILLINGACCSTATUS=ACQUIRED|BillCycle=22|BillFrequency=weekly|BillStartDate=14/12/2017|BillType=Consumer Detail|COLLECTIONS=N|CONTACTID=1-KQ0J6|CUSTOMERCODE=12457892|FinalStatusReason=|InvoiceArrIdNum=1112542453258'
)
select strtok(substr(data_val, position('ACCTNO'       in data_val) +  7), '|', 1) as acctno
     , strtok(substr(data_val, position('CUSTOMERCODE' in data_val) + 13), '|', 1) as custcode
     , substring(regexp_substr(data_val,'(?<=\|)ACCTNO=[^|]*')       from  8)      as acctno2
     , substring(regexp_substr(data_val,'(?<=\|)CUSTOMERCODE=[^|]*') from 14)      as custcode2
  from cte_data
;

Btw for substr / substring function, if you omit the last operator it will go until the end of the string.

Enthusiast

Re: search a string in text and get value between = and delimiter |

thank you Waldar.. I've slightly tweeked it to directly pick the value after '=' and before next '|' without using substr further.

SyntaxEditor Code Snippet

    ,REGEXP_SUBSTR(data_val ,'(?<=CUSTOMERCODE\=)(.*?)(?=\|)') cust4        ,REGEXP_SUBSTR(data_val ,'(?<=ACCTNO\=)(.*?)(?=\|)') acct3

  regards

Junior Contributor

Re: search a string in text and get value between = and delimiter |

Btw, there's a function to extract data from Name-Value_Pairs: NVP

Nvp(col_name,'CUSTOMERCODE','|', '=')
Nvp(col_name,'ACCTNO','|', '=')

Caution, it's case sensitive, so you might need to apply UPPER(col_name)