Add numbers within a string in an SQL statement

Question

I have below query of which i would like to modify.

I want to sum all the numbers that occur within a string with the condition that it is joined with the text GB or MB .

If it is in GB it first has to be converted to MB. (This i have done simply by multiplying by 1024)

SELECT /*+ PARALLEL */ 
    'SOME TEXT 20GB+2GB+SOMETEXT' SOMETEXT, 
    CASE  
      WHEN REGEXP_SUBSTR('SOME TEXT 20GB+2GB+SOMETEXT','GB',1,1) = 'GB'
      THEN  1024*to_number(regexp_replace(REGEXP_SUBSTR('SOME TEXT 20GB+2GB+SOMETEXT','(\d+)GB',1,1), '[^0-9]', ''))
    ELSE to_number(regexp_replace(REGEXP_SUBSTR('SOME TEXT 20GB+2GB+SOMETEXT','(\d+)MB',1,1), '[^0-9]', ''))
   END TOTAL_MBs
FROM DUAL;

TEST STRINGS

TEXT TEXT_35MB+ MORETEXT
OTHERTEXT 480MB + 3MB AND_TEXT
SOMETEXT 7MB + 7NUMBER
TEXT 1GB AND SOME_TEXT
SOME TEXT 20GB+2GB+SOMETEXT

Here is where i am stuck: To add the numbers that occur more than once in one text

For example:-

For this text OTHERTEXT 480MB + 3MB AND_TEXT I want my result to have 483 as TOTAL_MBS and not 480

Are you using SQL to create another programming language? :D — NikNik
– NikNik, Commented Feb 6, 2020 at 13:43
Sorry for the joke :) I mean that because you are trying to do weird things with SQL — NikNik
– NikNik, Commented Feb 6, 2020 at 13:46

Thomas Strub · Accepted Answer · 2020-02-06 14:10:47Z

Think you are searching for something like:

with da as (
              select 1 id, 'TEXT TEXT_35MB+ MORETEXT' tcase from dual
    union all select 2 id,'OTHERTEXT 480MB + 3MB AND_TEXT' tcase from dual
    union all select 3 id,'SOMETEXT 7MB + 7NUMBER' tcase from dual
    union all select 4 id,'TEXT 1GB AND SOME_TEXT' tcase from dual
    union all select 5 id,'SOME TEXT 20GB+2GB+SOMETEXT' tcase from dual
    union all select 6 id,'SOME TEXT 20MB+2GB+SOMETEXT' tcase from dual
    ),
split as( 
    select  id
         ,  tcase 
         ,  REGEXP_SUBSTR(tcase,'(\d+)(M|G)B',1,level) ot  
    from    da
    connect by REGEXP_SUBSTR(tcase,'(\d+)(M|G)B',1,level)is not null
    and     prior id = id
    and     PRIOR DBMS_RANDOM.VALUE IS NOT NULL)
select  id
      , tcase
      , sum( case when ot like '%GB%' then 1024 else 1 end * regexp_substr(ot,'\d+'))  v 
from    split 
group by id
        ,tcase
order by id;

Result:

1   TEXT TEXT_35MB+ MORETEXT             35
2   OTHERTEXT 480MB + 3MB AND_TEXT      483
3   SOMETEXT 7MB + 7NUMBER                7
4   TEXT 1GB AND SOME_TEXT             1024
5   SOME TEXT 20GB+2GB+SOMETEXT       22528
6   SOME TEXT 20MB+2GB+SOMETEXT        2068

MT0 · Accepted Answer · 2020-02-06 14:20:51Z

You can use a recursive sub-query factoring clause:

  SELECT sometext,
         COALESCE(
           REGEXP_SUBSTR( sometext, '(\d+)([MG])B', 1, 1, NULL, 1 )
           * CASE REGEXP_SUBSTR( sometext, '(\d+)([MG])B', 1, 1, NULL, 2 )
             WHEN 'M' THEN 1
             WHEN 'G' THEN 1024
             END,
           0
         ),
         1,
         REGEXP_COUNT( sometext, '(\d+)([MG])B' )
  FROM   test_data
UNION ALL
  SELECT sometext,
         total_mb
         + REGEXP_SUBSTR( sometext, '(\d+)([MG])B', 1, i + 1, NULL, 1 )
         * CASE REGEXP_SUBSTR( sometext, '(\d+)([MG])B', 1, i + 1, NULL, 2 )
           WHEN 'M' THEN 1
           WHEN 'G' THEN 1024
           END,
         i + 1,
         num_terms
  FROM   terms
  WHERE  i < num_terms
)
SELECT sometext,
       total_mb
FROM   terms
WHERE  i >= num_terms;

which for the test data:

CREATE TABLE test_data ( sometext ) AS
  SELECT 'SOME TEXT 20GB+2GB+SOMETEXT' FROM DUAL UNION ALL
  SELECT '1MB+1GB+10MB+10GB' FROM DUAL;

outputs:

SOMETEXT                    | TOTAL_MB
:-------------------------- | -------:
SOME TEXT 20GB+2GB+SOMETEXT |    22528
1MB+1GB+10MB+10GB           |    11275

db<>fiddle here

gwatene · Accepted Answer · 2020-02-07 11:33:01Z

1

below I used a view/memory table to assign regex function to the specific string and it worked for me

with tbl1 as (
              select  1 pd, ' 20GB+2GB sometext +7500 + 45sometext' string from dual 
        ),   
tbl2 as( 
    select  pd
         ,  string 
         ,  REGEXP_SUBSTR(string,'(\d+)(M|G)B',1,level) string2  
    from    tbl1
    connect by REGEXP_SUBSTR(string,'(\d+)(M|G)B',1,level)is not NULL
       and     prior pd = pd
    and     PRIOR DBMS_RANDOM.VALUE IS NOT NULL)
select  pd
      , string
      , sum( case when string2 like '%GB%' then 1024 end * regexp_substr(string2,'\d+'))  string3
from    tbl2 
group by pd
        ,string
order by pd;

answered Feb 7, 2020 at 11:33

gwatene

812 bronze badges

Collectives™ on Stack Overflow

Add numbers within a string in an SQL statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related