在oracle中修改列值if条件

Question

表中有以下值，找到时需要设置valid_values=6>6

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

因此，在改变后，所需的输出将如下，

SQL> /
FIS_ID VALUE           VALID_VALUES
---------- --------------- ---------------------------------------------
417     OFF             OFF,1,2,3,4,5,6,6,6,6,6

Answer 1

您不需要拆分和聚合；相反，您可以使用正则表达式来查找2位或2位以上的数字(即[1-9]d+(或高于6的1位值(即[789](，如果这些数字可能出现在您的数据集中，则可以包括前导零(因为您将数字存储为文本(：

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

对于样本数据：

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;

输出：

ID|VALUE|VALID_VALUES--：|：----|：----------------------555 |关闭|关闭，1,2,3,4,5,6,6,6,6666|OFF|OFF，1,2,3,4,5,6,05,6

db<gt；小提琴这里

Answer 2

您需要按如下方式进行拆分、替换和聚合：

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;

Answer 3

对于此解决方案，在替换和聚合之前，需要使用LAG分析函数进行拆分，如下所示：

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
;