SQL Regex - заменить подстрокой из другого поля

Это медленно, потому что вы расширяете каждую строку слишком много раз; предложения connect-by, которые вы используете, просматривают все строки, поэтому вы получаете огромное количество данных для последующей сортировки - вероятно, поэтому вы оказались там с DISTINCT.

Вы можете добавить два предложения PRIOR в connect-by, во-первых, чтобы сохранить ID_NO, а во-вторых, чтобы избежать цикла — для этого подойдет любая недетерминированная функция, я выбрал dbms_random.value, но вы можете использовать sys_guid, если хотите. , или что-то другое. Вам также не нужно много подзапросов, вы можете сделать это с двумя; или как CTE, которые, я думаю, немного яснее:

WITH feedback AS (
  SELECT 1001 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2' answers FROM DUAL UNION
  SELECT 1002 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '2|3' answers FROM DUAL UNION
  SELECT 1003 id_no, 'Apple Pie|Banana-Split|Cream Tea' options, '1|2|3' answers FROM DUAL
),
option_rows AS (
  SELECT
    id_no,
    LEVEL answer,
    REGEXP_SUBSTR(options, '[^|]+', 1, LEVEL) answer_text
  FROM feedback
  CONNECT BY LEVEL <= REGEXP_COUNT(options, '[^|]+')
  AND id_no = PRIOR id_no
  AND PRIOR dbms_random.value IS NOT NULL
),
answer_rows AS (
  SELECT
    id_no,
    REGEXP_SUBSTR(answers, '[^|]+', 1, LEVEL) answer
  FROM feedback
  CONNECT BY LEVEL <= REGEXP_COUNT(answers, '[^|]+')
  AND PRIOR id_no = id_no
  AND PRIOR dbms_random.value IS NOT NULL
)
SELECT
  option_rows.id_no,
  LISTAGG(option_rows.answer, '|') WITHIN GROUP (ORDER BY option_rows.answer) AS answers,
  LISTAGG(option_rows.answer_text, '|') WITHIN GROUP (ORDER BY option_rows.answer) AS answer_decode
FROM option_rows
JOIN answer_rows
ON option_rows.id_no = answer_rows.id_no
AND option_rows.answer = answer_rows.answer
GROUP BY option_rows.id_no
ORDER BY option_rows.id_no;

Что получает:

     ID_NO ANSWERS    ANSWER_DECODE                          
---------- ---------- ----------------------------------------
      1001 1|2        Apple Pie|Banana-Split                  
      1002 2|3        Banana-Split|Cream Tea                  
      1003 1|2|3      Apple Pie|Banana-Split|Cream Tea  

Я также изменил ваш шаблон регулярного выражения, поэтому вам не нужно добавлять или удалять |.

Оцените это компактное решение:

   with sample_data as
(
  select 'ala|ma|kota' options, '1|2' answers from dual
  union all
  select 'apples|oranges|bacon', '1|2|3' from dual
  union all
  select 'a|b|c|d|e|f|h|i','1|3|4|5|8' from dual
)
select answers, options,
regexp_replace(regexp_replace(options,'([^|]+)\|([^|]+)\|([^|]+)','\' || replace(answers,'|','|\')),'[|]+','|') answer_decode
from sample_data;

Выход:

  ANSWERS   OPTIONS              ANSWER_DECODE
--------- -------------------- ---------------------------
1|2       ala|ma|kota          ala|ma
1|2|3     apples|oranges|bacon apples|oranges|bacon
1|3|4|5|8 a|b|c|d|e|f|h|i      a|c|d|f|h|i

Я написал близкое решение в MySQL (сейчас не установлен Oracle), но я написал, что нужно изменить, чтобы запрос работал в Oracle.

Кроме того, самая уродливая часть моего кода будет намного лучше выглядеть в Oracle, поскольку в ней гораздо лучше реализована функция INSTR.

Идея состоит в том, чтобы выполнить ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ со списком чисел (от 1 до 10, чтобы поддерживать до 10 вариантов на опрос) и разбить поле ВАРИАНТЫ на разные строки... (вы делаете это, используя оба список чисел и функцию Oracle INSTR, см. комментарии).

Оттуда вы отфильтровываете строки, которые не были выбраны, и группируете все вместе.

-- I've used GROUP_CONCAT in MySQL, but in Oracle you'll have to use WM_CONCAT
select ID_NO, ANSWERS, group_concat(broken_down_options,'|') `OPTIONS`
from (
    select your_table.ID_NO, your_table.ANSWERS, 
            -- Luckily, you're using ORACLE so you can use an INSTR function that has the "occurrence" parameter
            -- INSTR(string, substring, [position, [occurrence]])
            -- use the nums.num field as input for the occurrence parameter
            -- and just put '1' under "position"
            case when nums.num = 1 
                then substr(your_table.`OPTIONS`, 1, instr(your_table.`OPTIONS`, '|') - 1)
                when nums.num = 2
                then substr(substr(your_table.`OPTIONS`, instr(your_table.`OPTIONS`, '|') + 1), 1, instr(substr(your_table.`OPTIONS`, instr(your_table.`OPTIONS`, '|') + 1), '|') - 1)
                else substr(your_table.`OPTIONS`,  length(your_table.`OPTIONS`) - instr(reverse(your_table.`OPTIONS`), '|') + 2) end broken_down_options
    from (select 1 num union all
        select 2 num union all
        select 3 num union all
        select 4 num union all
        select 5 num union all
        select 6 num union all
        select 7 num union all
        select 8 num union all
        select 9 num union all
        select 10 num
        ) nums 
        CROSS JOIN
        (select 1001 ID_NO, 'Apple Pie|Banana-Split|Cream Tea' `OPTIONS`, '1|2' ANSWERS union
        select 1002 ID_NO, 'Apple Pie|Banana-Split|Cream Tea' `OPTIONS`, '2|3' ANSWERS union
        select 1003 ID_NO, 'Apple Pie|Banana-Split|Cream Tea' `OPTIONS`, '1|2|3' ANSWERS
        ) your_table
    -- for example: 2|3 matches 2 and 3 but not 1
    where your_table.ANSWERS like concat(concat('%',nums.num),'%')
) some_query
group by ID_NO, ANSWERS

Создайте сохраненную предварительную настройку и выполните следующие шаги.

• Объявите массив вашего размера.
• Получить option данных из первой строки. Используйте регулярное выражение или level для извлечения значений между каналами, а затем сохраните их в массиве. Примечание. Это будет только один раз. Так что вам не нужно повторять это для каждой строки.
• Теперь в цикле для каждой строки выберите answers и используйте значения массива, чтобы назначить значения answers.