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

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

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

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

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

person AKBAR ALI    schedule 23.02.2015    source источник
comment
Ссылка выше исправлена, см. Разделить строку, разделенную одной запятой, на строки в Oracle   -  person Lalit Kumar B    schedule 13.08.2017


Ответы (8)


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

 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;

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

person David Faber    schedule 23.02.2015

Есть несколько вариантов. См. раздел Разделить одно строка с разделителями-запятыми в строки в Oracle

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

Используя любой из приведенных ниже SQL-запросов, вы можете включить их в FUNCTION.

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
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

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
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

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
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

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                  RULES ITERATE (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
------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

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

Читать DBMS_UTILITY.COMMA_TO_TABLE

person Lalit Kumar B    schedule 23.02.2015

Настройка Oracle:

CREATE OR REPLACE FUNCTION split_String(
  i_str    IN  VARCHAR2,
  i_delim  IN  VARCHAR2 DEFAULT ','
) RETURN SYS.ODCIVARCHAR2LIST DETERMINISTIC
AS
  p_result       SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  p_start        NUMBER(5) := 1;
  p_end          NUMBER(5);
  c_len CONSTANT NUMBER(5) := LENGTH( i_str );
  c_ld  CONSTANT NUMBER(5) := LENGTH( i_delim );
BEGIN
  IF c_len > 0 THEN
    p_end := INSTR( i_str, i_delim, p_start );
    WHILE p_end > 0 LOOP
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, p_end - p_start );
      p_start := p_end + c_ld;
      p_end := INSTR( i_str, i_delim, p_start );
    END LOOP;
    IF p_start <= c_len + 1 THEN
      p_result.EXTEND;
      p_result( p_result.COUNT ) := SUBSTR( i_str, p_start, c_len - p_start + 1 );
    END IF;
  END IF;
  RETURN p_result;
END;
/

Запрос

SELECT ROWNUM AS ID,
       COLUMN_VALUE AS Data
FROM   TABLE( split_String( 'A,B,C,D' ) );

Вывод:

ID DATA
-- ----
 1 A
 2 B
 3 C
 4 D
person MT0    schedule 24.02.2016

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

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 <= 100
          )
        WHERE lvl <= LENGTH(STR) - LENGTH(REPLACE(STR, ',')) - 1
        ORDER BY ID, NAME)
      LOOP
        IF i.ID = 1001 THEN
          l_res_coll.extend;
          l_index := l_res_coll.count;
          l_res_coll(l_index):= T_TABLE(i.ID, i.name);
        END IF;
      END LOOP;
      RETURN l_res_coll;
    END;
    /

Теперь мы можем выбрать из него:

select * from table(TEST_RETURN_TABLE()); 

Выход:

SQL> select * from table(TEST_RETURN_TABLE());

    FIELD1 FIELD2
---------- -------------------------
      1001 A
      1001 B
      1001 C
      1001 D
      1001 E
      1001 F

6 rows selected.

Очевидно, вам нужно заменить бит WITH TAB AS... на то, откуда вы будете получать фактические данные. Кредит Кредит

person mmmmmpie    schedule 23.02.2015
comment
и оба типа ur и функция показывают ошибку компиляции - person AKBAR ALI; 24.02.2015

Используйте эту функцию «Разделить»:

CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sys_refcursor is
v_res sys_refcursor;

begin
  open v_res for 
  WITH TAB AS 
  (SELECT p_str STR FROM DUAL)
  select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name 
  from
    ( select ',' || STR || ',' as STR from TAB ),
    ( select level as lvl from dual connect by level <= 100 )
    where lvl <= length(STR) - length(replace(STR, ',')) - 1;

     return v_res;
   end;

Вы не можете использовать эту функцию в операторе select, как вы описали в вопросе, но я надеюсь, что вы найдете ее полезной.

РЕДАКТИРОВАТЬ: Вот шаги, которые вам нужно сделать. 1. Создать объект: создать или заменить тип empy_type как объект (значение varchar2 (512)) 2. Создать тип: создать или заменить тип t_empty_type как таблицу empy_type 3. Создать функцию:

CREATE OR REPLACE FUNCTION Split (p_str varchar2) return sms.t_empty_type is
v_emptype t_empty_type := t_empty_type();
v_cnt     number := 0;
v_res sys_refcursor;
v_value nvarchar2(128);
begin
  open v_res for
  WITH TAB AS
  (SELECT p_str STR FROM DUAL)
  select substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl +     1) - instr(STR, ',', 1, lvl) - 1) name
  from
    ( select ',' || STR || ',' as STR from TAB ),
    ( select level as lvl from dual connect by level <= 100 )
    where lvl <= length(STR) - length(replace(STR, ',')) - 1;


  loop
     fetch v_res into v_value;
      exit when v_res%NOTFOUND;
      v_emptype.extend;
      v_cnt := v_cnt + 1;
     v_emptype(v_cnt) := empty_type(v_value);
    end loop;
    close v_res;

    return v_emptype;
end;

Затем просто позвоните так:

SELECT * FROM (TABLE(split('a,b,c,d,g'))) 
person Alexander Mchedlishvili    schedule 03.10.2016

Эта функция возвращает n-ю часть входной строки MYSTRING. Второй входной параметр — это разделитель, т. е. SEPARATOR_OF_SUBSTR, а третий параметр — N-я часть, которая требуется.

Примечание. MYSTRING должен заканчиваться разделителем.

create or replace FUNCTION PK_GET_NTH_PART(MYSTRING VARCHAR2,SEPARATOR_OF_SUBSTR VARCHAR2,NTH_PART NUMBER)
RETURN VARCHAR2
IS
NTH_SUBSTR VARCHAR2(500);
POS1 NUMBER(4);
POS2 NUMBER(4);
BEGIN
IF NTH_PART=1 THEN
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, 1)  INTO POS1 FROM DUAL; 
SELECT SUBSTR(MYSTRING,0,POS1-1) INTO NTH_SUBSTR FROM DUAL;
ELSE
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART-1) INTO  POS1 FROM DUAL; 
SELECT REGEXP_INSTR(MYSTRING,SEPARATOR_OF_SUBSTR, 1, NTH_PART)  INTO POS2 FROM DUAL; 
SELECT SUBSTR(MYSTRING,POS1+1,(POS2-POS1-1)) INTO NTH_SUBSTR FROM DUAL;
END IF;
RETURN NTH_SUBSTR;
END;

Надеюсь, это поможет кому-то, вы можете использовать эту функцию в цикле, чтобы разделить все значения:

SELECT REGEXP_COUNT(MYSTRING, '~', 1, 'i') INTO NO_OF_RECORDS FROM DUAL;
WHILE NO_OF_RECORDS>0
LOOP
    PK_RECORD    :=PK_GET_NTH_PART(MYSTRING,'~',NO_OF_RECORDS);
    -- do some thing
    NO_OF_RECORDS  :=NO_OF_RECORDS-1;
END LOOP;

Здесь NO_OF_RECORDS, PK_RECORD — временные переменные.

Надеюсь это поможет.

person Pavan Kumar Rao    schedule 28.06.2017

Лучший запрос Для разделения запятой в этом запросе мы конвертируем строки в столбцы...

SELECT listagg(BL_PRODUCT_DESC, ', ') within
   group(   order by BL_PRODUCT_DESC) PROD
  FROM GET_PRODUCT
--  WHERE BL_PRODUCT_DESC LIKE ('%WASH%')
  WHERE Get_Product_Type_Id = 6000000000007
person Muhammad Tazeem    schedule 09.12.2019

Попробуйте, как показано ниже

select 
    split.field(column_name,1,',','"') name1,
    split.field(column_name,2,',','"') name2
from table_name
person Ifthakharul Islam Dollar    schedule 25.05.2016
comment
Это Oracle, а не Sql Server. Смотрите теги. - person Gary_W; 25.05.2016