Функция разделения в oracle на значения, разделенные запятыми, с автоматической последовательностью

Question

Дополнительно

Источник Неподдерживаемая версия AMP Редактировать

Функция разделения в oracle на значения, разделенные запятыми, с автоматической последовательностью

Нужна функция Split, которая будет принимать два параметра, строку для разделения и разделитель, чтобы разделить строку и вернуть таблицу с колонками Id и Data.И как вызвать функцию Split, которая вернет таблицу с колонками Id и Data. Столбец Id будет содержать последовательность, а столбец Data - данные строки. Например.

SELECT*FROM Split('A,B,C,D',',')

Результат должен быть в следующем формате:

|Id | Data
 --   ----
|1  | A  |
|2  | B  |
|3  | C  |
|4  | D  |

Lalit Kumar B

Редактировал вопрос 23-го февраля 2015 в 4:05

Программирование

oracle function split plsql delimiter

23-го февраля 2015 в 3:20

26 просмотров

David Faber · Answer 1 · 2015-02-23T15:30:30+00:00

Вот как можно создать такую таблицу:

 SELECT LEVEL AS id, REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) AS data
   FROM dual
CONNECT BY REGEXP_SUBSTR('A,B,C,D', '[^,]+', 1, LEVEL) IS NOT NULL;

Немного подправив (т.е. заменив , в [^,] переменной), можно написать такую функцию, которая будет возвращать таблицу.

Lalit Kumar B · Answer 2 · 2015-02-23T15:49:03+00:00

Существует несколько вариантов. Смотрите Разделение одной строки, разделенной запятыми, на строки в Oracle.

Вам просто нужно добавить LEVEL в список select в качестве столбца, чтобы получить порядковый номер каждой возвращаемой строки. Или, ROWNUM также будет достаточно.

Используя любой из приведенных ниже SQL, вы можете включить их в ФУНКЦИЮ.

INSTR в предложении CONNECT BY:

SQL> WITH DATA AS
  2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3 )
  4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5 FROM DATA
  6 CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
  7 /
STR
слово1
слово2
слово3
слово4
слово5
слово6
Выбрано 6 рядов.
SQL>

REGEXP_SUBSTR в предложении CONNECT BY:

SQL> WITH DATA AS
  2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3 )
  4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5 FROM DATA
  6 CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL
  7 /
STR
слово1
слово2
слово3
слово4
слово5
слово6
Выбрано 6 рядов.
SQL>

REGEXP_COUNT в предложении CONNECT BY:

SQL> WITH DATA AS
  2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3 )
  4 SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5 FROM DATA
  6 CONNECT BY LEVEL

Использование XMLTABLE

SQL> WITH DATA AS
  2 ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3 )
  4 SELECT trim(COLUMN_VALUE) str
  5 FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))
  6 /
STR
------------------------------------------------------------------------
слово1
слово2
слово3
слово4
слово5
слово6
Выбрано 6 рядов.
SQL>

Используя предложение MODEL:

SQL> WITH t AS
  2 (
  3 SELECT 'word1, word2, word3, word4, word5, word6' str
  4 FROM dual ) ,
  5 model_param AS
  6 (
  7 SELECT str AS orig_str ,
  8 ','
  9 || str
 10 || ',' AS mod_str ,
 11 1 AS start_pos ,
 12 Length(str) AS end_pos ,
 13 (Length(str) - Length(Replace(str, ',')))) + 1 AS element_count ,
 14 0 AS element_no ,
 15 ROWNUM AS rn
 16 FROM t )
 17 SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
 18 FROM (
 19 SELECT *
 20 FROM model_param MODEL PARTITION BY (rn, orig_str, mod_str)
 21 DIMENSION BY (element_no)
 22 MEASURES (start_pos, end_pos, element_count)
 23 ПРАВИЛА ИТЕРАЦИИ (2000)
 24 UNTIL (ITERATION_NUMBER+1 = element_count[0])
 25 ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
 26 end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) ) )
 27 WHERE element_no != 0
 28 ORDER BY mod_str ,
 29 element_no
 30 /
STR
слово1
слово2
слово3
слово4
слово5
слово6
Выбрано 6 рядов.
SQL>

Вы также можете использовать пакет DBMS_UTILITY, предоставляемый Oracle. Он предоставляет различные подпрограммы. Одной из таких полезных утилит является процедура COMMA_TO_TABLE, которая преобразует список имен через запятую в таблицу имен PL/SQL.

Читать DBMS_UTILITY.COMMA_TO_TABLE

mmmmmpie · Answer 3 · 2015-02-23T15:47:51+00:00

Если вам нужна функция, попробуйте следующее.
Сначала мы создадим тип:

CREATE OR REPLACE TYPE T_TABLE IS OBJECT
(
    Field1 int
    , Field2 VARCHAR(25)
);
CREATE TYPE T_TABLE_COLL IS TABLE OF T_TABLE;
/

Затем создадим функцию:


CREATE OR REPLACE FUNCTION TEST_RETURN_TABLE
RETURN T_TABLE_COLL
    IS
      l_res_coll T_TABLE_COLL;
      l_index number;
    BEGIN
      l_res_coll := T_TABLE_COLL();
      FOR i IN (
        WITH TAB AS
          (SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
          UNION
          SELECT '1002' ID, 'D,E,F' STR FROM DUAL
          UNION
          SELECT '1003' ID, 'C,E,G' STR FROM DUAL
          )
        SELECT id,
          SUBSTR(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name
        FROM
          ( SELECT ',' || STR || ',' AS STR, id FROM TAB
          ),
          ( SELECT level AS lvl FROM dual CONNECT BY level