Анализ значений с разделителями с использованием REGEXP_SUPSTR в Oracle 10g

Question

У меня есть таблица с именем TVL_DETAIL, которая содержит столбец TVL_CD_LIST. Столбец TVL_CD_LIST содержит три записи:
TVL_CD_LIST:
M1180_Z6827
K5900_Z6828
I2510

Я использовал следующий код в попытке вернуть только значения (исключая подчеркивание):

SELECT
TVL_CD_LIST   
FROM TVL_DETAIL  
WHERE TVL_CD_LIST IN (SELECT regexp_substr(TVL_CD_LIST,'[^_]+', 1, level) FROM DUAL
CONNECT BY regexp_substr(TVL_CD_LIST,'[^_]+', 1, level) IS NOT NULL)

То, что я ожидал увидеть возвращенным в отдельных строках, было:
M1180
Z6827
K5900
Z6828
I2510

Но он возвращает только I2510(исходное значение, которое не содержит подчеркивания).

Что я делаю неправильно? Любая помощь приветствуется. Спасибо!

Answer 1

Чтобы ответить на ваш вопрос, вы запрашиваете список, в котором он соответствует подэлементу, и это произойдет только в том случае, если список состоит из одного элемента. Что вы действительно хотели выбрать, так это сами подэлементы.

Примечание. Объяснение причин разбора строк с использованием формы регулярного выражения. '[^_]+' здесь плохо: /questions/36255452/razdelit-zapyatyie-znacheniya-v-stolbtsyi-v-oracle/36255467#36255467

Вы хотите проанализировать список, выбрав элементы:

SQL> with TVL_DETAIL(TVL_CD_LIST) as (
     select 'M1180_Z6827' from dual union
     select 'K5900_Z6828' from dual union
     select 'I2510' from dual
   )
   SELECT distinct regexp_substr(TVL_CD_LIST, '(.*?)(_|$)', 1, level, NULL, 1) element
   FROM TVL_DETAIL
   CONNECT BY level <= LENGTH(regexp_replace(TVL_CD_LIST, '[^_]', '')) + 1;
   -- 11g  CONNECT BY level <= regexp_count(TVL_CD_LIST, '_') + 1;

ELEMENT
-----------
Z6827
K5900
M1180
I2510
Z6828

SQL>

И это здорово, если вы хотите отслеживать по строке и элементу в строке:

SQL> with TVL_DETAIL(row_nbr, TVL_CD_LIST) as (
     select 1, 'M1180_Z6827' from dual union
     select 2, 'K5900_Z6828' from dual union
     select 3, 'I2510' from dual
   )
   SELECT row_nbr, column_value substring_nbr,
          regexp_substr(TVL_CD_LIST, '(.*?)(_|$)', 1, column_value, NULL, 1) element
   FROM TVL_DETAIL,
     TABLE(
       CAST(
         MULTISET(SELECT LEVEL
                  FROM dual
                  CONNECT BY level <= LENGTH(regexp_replace(TVL_CD_LIST, '[^_]', '')) + 1
                  -- 11g CONNECT BY LEVEL <= REGEXP_COUNT(TVL_CD_LIST, '_')+1
                  ) AS sys.OdciNumberList
           )
     )
   order by row_nbr, substring_nbr;

   ROW_NBR SUBSTRING_NBR ELEMENT
---------- ------------- -----------
         1             1 M1180
         1             2 Z6827
         2             1 K5900
         2             2 Z6828
         3             1 I2510

SQL>

РЕДАКТИРОВАТЬ: Упс, отредактировано для работы с 10g, так как REGEXP_COUNT недоступен до 11g.

Answer 2

Используемый вами запрос создает список, но вы сравниваете список записей с самим столбцом, используя in оговорка как таковая M1180 или же Z6827 не может быть равным M1180_Z6827 и так для K5900_Z6828, I2510 имеет только одно значение, поэтому оно сопоставляется.

Вы можете использовать запрос ниже, если ваше требование именно то, что вы упомянули в желаемом выводе.

SQL> WITH tvl_detail AS
      2   (SELECT 'M1180_Z6827' tvl_cd_list FROM dual
      3    UNION ALL
      4    SELECT 'K5900_Z6828' FROM dual
      5    UNION ALL
      6    SELECT 'I2510' FROM dual)
      7   ---------------------------
      8   --- End of data preparation
      9   ---------------------------
     10  SELECT regexp_substr(tvl_cd_list, '[^_]+', 1, LEVEL) AS tvl_cd_list
     11    FROM tvl_detail
     12  CONNECT BY regexp_substr(tvl_cd_list, '[^_]+', 1, LEVEL) IS NOT NULL
     13         AND PRIOR tvl_cd_list = tvl_cd_list
     14         AND PRIOR sys_guid() IS NOT NULL;

ВЫХОД:

TVL_CD_LIST
--------------------------------------------
I2510
K5900
Z6828
M1180
Z6827