Skip to Main Content
  • Questions
  • Performing sum of all matched substrings from a string using regular expression

Breadcrumb

Question and Answer

Chris Saxon

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.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.