Thanks for the question, Siva.
Asked: November 20, 2020 - 4:37 am UTC
Answered by: Chris Saxon - Last updated: November 20, 2020 - 9:24 am UTC
Category: SQL - Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Viewed 100+ times
You Asked
I have a database table with name T_Usage and column name general_1. The general_1 field consists of below value.
14348860:1T:24:|120|1120|2000*14348860:1T:24:|120|1220|3000*14348860:1T:24:|120|1120|879609299148
I have to perform the sum of substrings enclosed between |(Pipe) and *(asterisk) .In the above input value we have two such substrings (2000,3000).
using regexp_substr() function, I am able to identify first substring only.
select regexp_substr('input', '\|([0-9])+\*') test from dual;How to identify all occurrences and perform addition. Please provide me SQL query if possible.
Expected output should be = (2000 + 3000)
= 5000
and we said...
Use your favourite CSV-to-rows method to generate a row for each instance of the pattern in the search string.
For example, I've used the connect by level trick in a lateral subquery, using regexp_count to find how many times this pattern is in the input:
with rws as (
select '14348860:1T:24:|120|1120|2000*14348860:1T:24:|120|1220|3000*14348860:1T:24:|120|1120|879609299148' str
from dual
)
select regexp_substr ( str, '\|([0-9])+\*', 1, n )
from rws, lateral (
select level n from dual
connect by level <= regexp_count ( str, '\|([0-9])+\*' )
);
REGEXP_SUBSTR(STR,'\|([0-9])+\*',1,N)
|2000*
|3000*
From there you just need to remove the first and last characters and sum() up the result.