CODEWITHSUNDEEP
sqloracle-database
jayreado
jayreado

Reputation: 5

modify column value if condition in oracle

I have below values in table, and need to set valid_values =6 when found >6

  ID        VALUE           VALID_VALUES
---------- --------------- ---------------------------------------------
       555 OFF              OFF,1,2,3,4,5,6,7,8,9,10

So after change desired output would be as below,

SQL> /

    FIS_ID VALUE           VALID_VALUES
---------- --------------- ---------------------------------------------

   417     OFF             OFF,1,2,3,4,5,6,6,6,6,6

Upvotes: 0

Views: 417

Answers (3)

MT0
MT0

Reputation: 168051

You do not need to split and aggregate; instead you can use a regular expression to find either 2-or-more-digit numbers (i.e. [1-9]\d+) or 1-digit values higher than 6 (i.e. [789]) and could include leading zeroes if these may appear in your data set (since you are storing numbers as text):

SELECT id,
       value,
       REGEXP_REPLACE(
         valid_values,
         '0*[1-9]\d+|0*[789]',
         '6'
       ) AS valid_values
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name ( ID, VALUE, VALID_VALUES ) AS
SELECT 555, 'OFF', 'OFF,1,2,3,4,5,6,7,8,9,10'   FROM DUAL UNION ALL
SELECT 666, 'OFF', 'OFF,1,2,3,4,5,6,42,05,0123' FROM DUAL;

Outputs:

 ID | VALUE | VALID_VALUES           
--: | :---- | :----------------------
555 | OFF   | OFF,1,2,3,4,5,6,6,6,6,6
666 | OFF   | OFF,1,2,3,4,5,6,6,05,6

db<>fiddle here

Upvotes: 2

Mahamoutou
Mahamoutou

Reputation: 1555

For this solution, you need to split using LAG analytic function, before replacing and aggregating as below :

select ID, VALUE
  , listagg(
      case when regexp_like(separate_value, '^\d+$') 
        then case when separate_value > 6 
              then '6' 
              else separate_value 
            end
        else separate_value 
      end
      , ',') within group (order by lvl) VALID_VALUES
from (
select ID, VALUE
    , lvl, substr(VALID_VALUES, lag(pos, 1, 0)over(order by lvl)+1, pos - lag(pos, 1, 0)over(order by lvl)-1) separate_value
from (
  select ID, VALUE, VALID_VALUES||','VALID_VALUES, level lvl, instr(VALID_VALUES||',', ',', 1, level)pos
  from your_table
  connect by level <= length(VALID_VALUES||',')-length(replace(VALID_VALUES||',', ','))
  )
)
group by ID, VALUE
;

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You need to split, replace and aggregate as follows:

Select id, value, 
       Listagg(case when to_number(vals default null on conversion error) is not null 
                    then case when to_number(vals) > 6 then 6 else vals end 
              else vals end) Within group (order by lvl) as valid_values
 From
(Select id, value, 
        REGEXP_SUBSTR( t.valid_values, '[^,]+', 1, column_value ) ) , ',' ) as vals, 
        column_value as lvl
   from your_table t,
       TABLE(CAST(MULTISET(
             SELECT level as lvl
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.valid_value, '[^,]+' )
            AS SYS.ODCIVARCHAR2LIS  ) v
 ) group by id, value;

Upvotes: 0

Related Questions