DEVHIDE
Login Or Sign up

How to use a case statement in regexp_replace function？

84 Views Asked by At

I am trying to use case statement in regexp_replace function, Because I want to replace it according to different situations. The SQL looks like that:

col str for a15
with temp as
(
select 'A*((B*C)+D)' expre1 from dual union all
select 'A*((B*C)-D)' expre1 from dual union all
select '((A*B)+C)*D' expre1 from dual union all
select '((A*B)+C)+D' expre1 from dual union all
select 'A*((B+C)+D)' expre1 from dual union all
select '((A*B)+C)-D' expre1 from dual union all
select 'A*((B+C)-D)' expre1 from dual union all
select '((A*B)+C)/D' expre1 from dual union all
select '((A*B)-C)*D' expre1 from dual union all
select '((A*B)-C)+D' expre1 from dual union all
select 'A*((B-C)+D)' expre1 from dual union all
select 'A*((B-C)-D)' expre1 from dual union all
select '((A*B)-C)-D' expre1 from dual union all
select '((A*B)-C)/D' expre1 from dual union all
select 'A*((B/C)+D)' expre1 from dual union all
select 'A*((B/C)-D)' expre1 from dual union all
select 'A+((B*C)+D)' expre1 from dual union all
select 'A+((B*C)-D)' expre1 from dual union all
select '((A+B)+C)*D' expre1 from dual union all
select 'A+((B+C)+D)' expre1 from dual union all
select '((A+B)+C)+D' expre1 from dual union all
select 'A+((B+C)-D)' expre1 from dual union all
select '((A+B)+C)-D' expre1 from dual union all
select '((A+B)+C)/D' expre1 from dual union all
select '((A+B)-C)*D' expre1 from dual union all
select 'A+((B-C)+D)' expre1 from dual union all
select '((A+B)-C)+D' expre1 from dual union all
select '((A+B)-C)-D' expre1 from dual union all
select 'A+((B-C)-D)' expre1 from dual union all
select '((A+B)-C)/D' expre1 from dual union all
select 'A+((B/C)+D)' expre1 from dual union all
select 'A+((B/C)-D)' expre1 from dual union all
select 'A-((B*C)+D)' expre1 from dual union all
select 'A-((B*C)-D)' expre1 from dual union all
select '((A-B)+C)*D' expre1 from dual union all
select 'A-((B+C)+D)' expre1 from dual union all
select '((A-B)+C)+D' expre1 from dual union all
select 'A-((B+C)-D)' expre1 from dual union all
select '((A-B)+C)-D' expre1 from dual union all
select '((A-B)+C)/D' expre1 from dual union all
select '((A-B)-C)*D' expre1 from dual union all
select 'A-((B-C)+D)' expre1 from dual union all
select '((A-B)-C)+D' expre1 from dual union all
select 'A-((B-C)-D)' expre1 from dual union all
select '((A-B)-C)-D' expre1 from dual union all
select '((A-B)-C)/D' expre1 from dual union all
select 'A-((B/C)+D)' expre1 from dual union all
select 'A-((B/C)-D)' expre1 from dual union all
select 'A/((B*C)+D)' expre1 from dual union all
select 'A/((B*C)-D)' expre1 from dual union all
select '((A/B)+C)*D' expre1 from dual union all
select 'A/((B+C)+D)' expre1 from dual union all
select '((A/B)+C)+D' expre1 from dual union all
select '((A/B)+C)-D' expre1 from dual union all
select 'A/((B+C)-D)' expre1 from dual union all
select '((A/B)+C)/D' expre1 from dual union all
select '((A/B)-C)*D' expre1 from dual union all
select 'A/((B-C)+D)' expre1 from dual union all
select '((A/B)-C)+D' expre1 from dual union all
select 'A/((B-C)-D)' expre1 from dual union all
select '((A/B)-C)-D' expre1 from dual union all
select '((A/B)-C)/D' expre1 from dual union all
select 'A/((B/C)+D)' expre1 from dual union all
select 'A/((B/C)-D)' expre1 from dual
)
select expre1
       ,regexp_replace(expre1,'\(\(([ABCD])([\*/\+\-])([ABCD])\)([\*/\+\-])([ABCD])\)',
        case
          when '\4' = '+' or '\4' = '-' then '(\1\2\3\4\5)'
          else '((\1\2\3)\4\5)'
        end)  str
from temp
where regexp_instr(expre1,'\(\(([ABCD])([\*/\+\-])([ABCD])\)([\*/\+\-])([ABCD])\)') > 0;

bug the result is as follows：

EXPRE1                    STR
------------------------- ---------------
A*((B*C)+D)               A*((B*C)+D)
A*((B*C)-D)               A*((B*C)-D)
((A*B)+C)*D               ((A*B)+C)*D
((A*B)+C)+D               ((A*B)+C)+D
A*((B+C)+D)               A*((B+C)+D)
((A*B)+C)-D               ((A*B)+C)-D
A*((B+C)-D)               A*((B+C)-D)

It's looks that the code "when '\4'" considered as a string '\4' but not regular expression. My goal is to remove parentheses. So, how can I modify it?

oracle case regexp-replace
Original Q&A
2

There are 2 best solutions below

0
d r On

Maybe it could be done without regexp. With your sample data - something like this

Select  EXPRE1,
        Case  When Instr(EXPRE1, '-(') > 0 Then EXPRE1
              When Instr(EXPRE1, '*') = 0 And Instr(EXPRE1, '/') = 0
                  Then '(' || Replace(Replace(EXPRE1, '(', ''), ')', '') || ')'
              When SubStr(EXPRE1, 4, 1) In('+', '-') And Instr(EXPRE1, '*', 1, 1) = Length(EXPRE1) - 1 And Instr(EXPRE1, '/') = 0
                  Then  '(' || 
                        Replace(
                          Replace(Replace(Replace(Replace(EXPRE1, '(', ''), ')', ''), ')+', '+'), ')-', '-')
                        , '*', ')*')
              When Instr(EXPRE1, '*', 1, 1) = 2 And Instr(EXPRE1, '*', 1, 2) = 0 And Instr(EXPRE1, '/') = 0
                  Then SubStr(EXPRE1, 1,  2) || '(' || Replace(Replace(SubStr(EXPRE1, 3), '(', ''), ')', '') || ')'
              When Instr(EXPRE1, '*', 1, 1) < Length(EXPRE1) - 1 And Instr(EXPRE1, '/') = 0
                  Then EXPRE1
              --
              When SubStr(EXPRE1, 4, 1) In('+', '-') And Instr(EXPRE1, '/', 1, 1) = Length(EXPRE1) - 1 And Instr(EXPRE1, '*') = 0
                  Then  '(' || 
                        Replace(
                          Replace(Replace(Replace(Replace(EXPRE1, '(', ''), ')', ''), ')+', '+'), ')-', '-')
                        , '/', ')/')
              When Instr(EXPRE1, '/', 1, 1) = 2 And Instr(EXPRE1, '/', 1, 2) = 0 And Instr(EXPRE1, '*') = 0
                  Then SubStr(EXPRE1, 1,  2) || '(' || Replace(Replace(SubStr(EXPRE1, 3), '(', ''), ')', '') || ')'
              --
              When Length(EXPRE1) - Length(Replace(EXPRE1, '*')) > 1 OR Length(EXPRE1) - Length(Replace(EXPRE1, '/')) > 1 OR
                  (Length(EXPRE1) - Length(Replace(EXPRE1, '*')) = 1 And Length(EXPRE1) - Length(Replace(EXPRE1, '/')) = 1)
                  Then EXPRE1
        Else  '((' || 
              SubStr(Replace(EXPRE1, '((', ''), 1, InStr(Replace(EXPRE1, '((', ''), ')', 1, 1)) ||
              SubStr(Replace(EXPRE1, '((', ''), InStr(Replace(EXPRE1, '((', ''), ')', 1, 1) + 1)
        End "STR"
From    temp

/*
EXPRE1      STR                                     
----------- ----------------------------------------
A*((B*C)+D) A*((B*C)+D)                             
A*((B*C)-D) A*((B*C)-D)                             
((A*B)+C)*D ((A*B)+C)*D                             
((A*B)+C)+D ((A*B)+C)+D                             
A*((B+C)+D) A*(B+C+D)                               
((A*B)+C)-D ((A*B)+C)-D                             
A*((B+C)-D) A*(B+C-D)                               
((A*B)+C)/D ((A*B)+C)/D                             
((A*B)-C)*D ((A*B)-C)*D                             
((A*B)-C)+D ((A*B)-C)+D                             
A*((B-C)+D) A*(B-C+D)                               
A*((B-C)-D) A*(B-C-D)                               
((A*B)-C)-D ((A*B)-C)-D                             
((A*B)-C)/D ((A*B)-C)/D                             
A*((B/C)+D) A*((B/C)+D)                             
A*((B/C)-D) A*((B/C)-D)                             
A+((B*C)+D) A+((B*C)+D)                             
A+((B*C)-D) A+((B*C)-D)                             
((A+B)+C)*D (A+B+C)*D                               
A+((B+C)+D) (A+B+C+D)                               
((A+B)+C)+D (A+B+C+D)                               
A+((B+C)-D) (A+B+C-D)                               
((A+B)+C)-D (A+B+C-D)                               
((A+B)+C)/D (A+B+C)/D                               
((A+B)-C)*D (A+B-C)*D                               
A+((B-C)+D) (A+B-C+D)                               
((A+B)-C)+D (A+B-C+D)                               
((A+B)-C)-D (A+B-C-D)                               
A+((B-C)-D) (A+B-C-D)                               
((A+B)-C)/D (A+B-C)/D                               
A+((B/C)+D) ((A+B/C)+D)                             
A+((B/C)-D) ((A+B/C)-D)                             
A-((B*C)+D) A-((B*C)+D)                             
A-((B*C)-D) A-((B*C)-D)                             
((A-B)+C)*D (A-B+C)*D                               
A-((B+C)+D) A-((B+C)+D)                             
((A-B)+C)+D (A-B+C+D)                               
A-((B+C)-D) A-((B+C)-D)                             
((A-B)+C)-D (A-B+C-D)                               
((A-B)+C)/D (A-B+C)/D                               
((A-B)-C)*D (A-B-C)*D                               
A-((B-C)+D) A-((B-C)+D)                             
((A-B)-C)+D (A-B-C+D)                               
A-((B-C)-D) A-((B-C)-D)                             
((A-B)-C)-D (A-B-C-D)                               
((A-B)-C)/D (A-B-C)/D                               
A-((B/C)+D) A-((B/C)+D)                             
A-((B/C)-D) A-((B/C)-D)                             
A/((B*C)+D) A/((B*C)+D)                             
A/((B*C)-D) A/((B*C)-D)                             
((A/B)+C)*D ((A/B)+C)*D                             
A/((B+C)+D) A/(B+C+D)                               
((A/B)+C)+D ((A/B)+C)+D                             
((A/B)+C)-D ((A/B)+C)-D                             
A/((B+C)-D) A/(B+C-D)                               
((A/B)+C)/D ((A/B)+C)/D                             
((A/B)-C)*D ((A/B)-C)*D                             
A/((B-C)+D) A/(B-C+D)                               
((A/B)-C)+D ((A/B)-C)+D                             
A/((B-C)-D) A/(B-C-D)                               
((A/B)-C)-D ((A/B)-C)-D                             
((A/B)-C)/D ((A/B)-C)/D                             
A/((B/C)+D) A/((B/C)+D)                             
A/((B/C)-D) A/((B/C)-D)                           */

NOTE: Probably Didn't cover all the specific cases, but you'll get the picture ...
You can test if the results are same if you add a small function to WITH clause. The query above will be cte conversions in the code below:

WITH  
Function math(p_formula VarChar2) Return Number AS
  BEGIN
      Declare 
          cmd             VarChar2(512) := 'Select ' || p_formula || ' From Dual' ;
          chk             Number;
          v_formula       VarChar2(512) := p_formula;
      Begin
               execute immediate cmd InTo chk;
              RETURN chk;
      End;
  END;
    conversions AS 
      ( ... code from above sql ... 
      )
Select *
From (  SELECT  EXPRE1, STR, 
                Round(Math(Replace(Replace(Replace(Replace(EXPRE1, 'A', '1'), 'B', '2'), 'C', '3'), 'D', '4') ), 2) "VAL_EXPRE1",
                Round(Math(Replace(Replace(Replace(Replace(STR, 'A', '1'), 'B', '2'), 'C', '3'), 'D', '4') ), 2) "VAL_STR",
                Round(Math(Replace(Replace(Replace(Replace(EXPRE1, 'A', '1'), 'B', '2'), 'C', '3'), 'D', '4') ), 2) -
                Round(Math(Replace(Replace(Replace(Replace(STR, 'A', '1'), 'B', '2'), 'C', '3'), 'D', '4') ), 2) "DIFF"
        From  conversions
      )
Where DIFF != 0

... all the results are equal

--    no rows selected
0
MT0 On

In Oracle, your regular expression is not correct as [\*/\+\-] matches either \ or * or / or \ or + or \ or -. What you want is just [*/+-].

While your code is valid syntax, it is not going to give you the output you expect as the CASE expression will not be evaluated as part of the regular expression so when '\4' = '+' or '\4' = '-' is never going to be true as \4 will be evaluated as the literal string \4 and not as the 4th match parameter returned from the regular expression.

You can use two mutually-exclusive regular expressions matching [*/] in one and [+-] in the second:

SELECT expre1,
       REGEXP_REPLACE(
         REGEXP_REPLACE(
           expre1,
           '\(\(([ABCD][*/+-][ABCD])\)([+-][ABCD])\)',
           '(\1\2)'
         ),
         '\(\(([ABCD][*/+-][ABCD])\)([*/][ABCD])\)',
         '((\1)\2)'
       ) AS str
FROM   temp
WHERE  REGEXP_LIKE(expre1,'\(\(([ABCD][*+-][ABCD])\)([*/+-][ABCD])\)');

Which, for the sample data, outputs:

EXPRE1 STR
A*((B*C)+D) A*(B*C+D)
A*((B*C)-D) A*(B*C-D)
((A*B)+C)*D (A*B+C)*D
((A*B)+C)+D (A*B+C)+D
A*((B+C)+D) A*(B+C+D)
((A*B)+C)-D (A*B+C)-D
A*((B+C)-D) A*(B+C-D)
((A*B)+C)/D (A*B+C)/D
((A*B)-C)*D (A*B-C)*D
((A*B)-C)+D (A*B-C)+D
A*((B-C)+D) A*(B-C+D)
A*((B-C)-D) A*(B-C-D)
((A*B)-C)-D (A*B-C)-D
((A*B)-C)/D (A*B-C)/D
A+((B*C)+D) A+(B*C+D)
A+((B*C)-D) A+(B*C-D)
((A+B)+C)*D (A+B+C)*D
A+((B+C)+D) A+(B+C+D)
((A+B)+C)+D (A+B+C)+D
A+((B+C)-D) A+(B+C-D)
((A+B)+C)-D (A+B+C)-D
((A+B)+C)/D (A+B+C)/D
((A+B)-C)*D (A+B-C)*D
A+((B-C)+D) A+(B-C+D)
((A+B)-C)+D (A+B-C)+D
((A+B)-C)-D (A+B-C)-D
A+((B-C)-D) A+(B-C-D)
((A+B)-C)/D (A+B-C)/D
A-((B*C)+D) A-(B*C+D)
A-((B*C)-D) A-(B*C-D)
((A-B)+C)*D (A-B+C)*D
A-((B+C)+D) A-(B+C+D)
((A-B)+C)+D (A-B+C)+D
A-((B+C)-D) A-(B+C-D)
((A-B)+C)-D (A-B+C)-D
((A-B)+C)/D (A-B+C)/D
((A-B)-C)*D (A-B-C)*D
A-((B-C)+D) A-(B-C+D)
((A-B)-C)+D (A-B-C)+D
A-((B-C)-D) A-(B-C-D)
((A-B)-C)-D (A-B-C)-D
((A-B)-C)/D (A-B-C)/D
A/((B*C)+D) A/(B*C+D)
A/((B*C)-D) A/(B*C-D)
A/((B+C)+D) A/(B+C+D)
A/((B+C)-D) A/(B+C-D)
A/((B-C)+D) A/(B-C+D)
A/((B-C)-D) A/(B-C-D)

fiddle

Related Questions in ORACLE

Related Questions in CASE

Related Questions in REGEXP-REPLACE

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.