Разделить запятые значения в столбцы в Oracle

Question

У меня есть значения, возвращаемые с 255 значениями, разделенными запятыми. Есть ли простой способ разбить их на столбцы, не имея 255 подстрок?

ROW  | VAL
----------- 
1    | 1.25, 3.87, 2, ... 
2    | 5, 4, 3.3, ....

в

ROW | VAL | VAL | VAL ...
--------------------- 
1   |1.25 |3.87 | 2 ...     
2   | 5   | 4   | 3.3 ...

Answer 1

Ты можешь использовать regexp_substr():

select regexp_substr(val, '[^,]+', 1, 1) as val1, 
       regexp_substr(val, '[^,]+', 1, 2) as val2, 
       regexp_substr(val, '[^,]+', 1, 3) as val3, 
       . . .

Я бы посоветовал вам сгенерировать столбец из 255 чисел в Excel (или другой электронной таблице) и использовать электронную таблицу для создания кода SQL.

Answer 2

Осторожно! Выражение regexp_substr формата '[^,]+' не вернет ожидаемое значение, если в списке есть нулевой элемент, и вы хотите этот элемент или один после него. Рассмотрим этот пример, где 4-й элемент равен NULL, и я хочу, чтобы 5-й элемент и, таким образом, ожидал возврата "5":

SQL> select regexp_substr('1,2,3,,5,6', '[^,]+', 1, 5) from dual;

R
-
6

Сюрприз! Он возвращает 5-й NON-NULL элемент, а не фактический 5-й элемент! Возвращены неверные данные, и вы можете их даже не поймать. Попробуйте это вместо этого:

SQL> select regexp_substr('1,2,3,,5,6', '(.*?)(,|$)', 1, 5, NULL, 1) from dual;

R
-
5

Таким образом, исправленное выше REGEXP_SUBSTR говорит, что нужно искать 5-е вхождение 0 или более символов, разделенных запятой, за которыми следует запятая или конец строки (допускается следующий разделитель, будь то запятая или конец строки), и если найдено, верните 1-ую подгруппу (данные НЕ включают запятую или конец строки).

Шаблон поиска соответствия '(.*?)(,|$)' пояснил:

(             = Start a group
.             = match any character
*             = 0 or more matches of the preceding character
?             = Match 0 or 1 occurrences of the preceding pattern
)             = End the 1st group
(             = Start a new group (also used for logical OR)
,             = comma
|             = OR
$             = End of the line
)             = End the 2nd group

РЕДАКТИРОВАТЬ: Дополнительная информация добавлена ​​и упростила регулярное выражение.

См. Этот пост для получения дополнительной информации и предложения об инкапсуляции этого в функцию для легкого повторного использования: REGEX для выбора n-го значения из списка, с учетом нулей Это пост, где я обнаружил формат '[^,]+' есть проблема. К сожалению, это формат регулярных выражений, который вы чаще всего будете видеть в качестве ответа на вопросы о том, как анализировать список. Мне страшно подумать обо всех неверных данных, возвращаемых '[^,]+'!

Answer 3

Если у вас есть только одна строка и время для создания

• создать свой собственный встроенный cto_table функция для разделения строки на любой разделитель, то вы можете использовать PIVOT + LISTAGG сделать это следующим образом:

---

select * from (
  select rownum r , collection.*  
    from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection
)
PIVOT ( 
  LISTAGG(column_value) within group (order by 1) as val 
  for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)

---

К вашему сведению: вот как создать cto_table функция:

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END cto_table;
/

Answer 4

Иерархический запрос может быть использован. Поворот может быть сделан с делом и группой.

with value_t as
( 
  select row_t,row_number() OVER (partition by row_t order by rownum )rn,
  regexp_substr(val, '[^,]+', 1, LEVEL) val from Table1
CONNECT BY LEVEL <= regexp_count(val, '[^,]+') 
AND prior row_t = row_t 
AND prior sys_guid() is not null
  ) select row_t, max( case when rn = 1 THEN val end ) val_1,
  max( case when rn = 2 THEN val end ) val_2,
  max( case when rn = 3 THEN val end ) val_3
  from value_t
  group by row_t;