你的逻辑似乎可以简化为：

• 如果字符串包含1和2到49之间的至少一个数字，则从字符串中删除1。
• 然后，对于所有字符串，将所有下划线字符替换为管道字符。

其可以被实现为：

SELECT value,
       REPLACE(
         CASE
         WHEN '_' || value || '_' LIKE '%_1_%'                -- Contains 1
         AND  REGEXP_LIKE(value, '(^|_)([2-9]|[1-4]\d)($|_)') -- Contains 2-49
         THEN TRIM(
                BOTH '_' FROM 
                REPLACE(
                  REPLACE(
                    '_' || REPLACE(value, '_', '__') || '_',
                    '_1_'
                  ),
                  '__',
                  '_'
                )
              )
         ELSE value
         END,
         '_',
         '|'
       ) AS updated_value
FROM   table_name

其中，对于样本数据：

CREATE TABLE table_name (value) AS
SELECT '1_2_5_11_23_48_55_67_98' FROM DUAL UNION ALL
SELECT '2_49_1' FROM DUAL UNION ALL
SELECT '99_1_50' FROM DUAL UNION ALL
SELECT '2_3_49' FROM DUAL UNION ALL
SELECT '51_51_98_99_97' FROM DUAL UNION ALL
SELECT '1' FROM DUAL UNION ALL
SELECT '99_2' FROM DUAL;

输出：
| 价值|更新值|
| --------------|--------------|
| 1_2_5_11_23_48_55_67_98||25|11|23|48|55|67|98|
| 2_49_1||二四九|
| 99_1_50|九九||一五零|
| 2_3_49||二三|四十九|
| 51_51_98_99_97|51|51|98|99|97|
| 1|1|
| 99_2|九九|二|
fiddle