Разбиение строки на несколько строк в Oracle

81

Я знаю, что на этот вопрос в некоторой степени ответили PHP и MYSQL, но мне было интересно, может ли кто-нибудь научить меня простейшему подходу к разбиению строки (через запятую) на несколько строк в Oracle 10g (предпочтительно) и 11g.

Таблица выглядит следующим образом:

Name | Project | Error 
108    test      Err1, Err2, Err3
109    test2     Err1

Я хочу создать следующее:

Name | Project | Error
108    Test      Err1
108    Test      Err2 
108    Test      Err3 
109    Test2     Err1

Я видел несколько потенциальных решений для стека, однако они учитывали только один столбец (являющийся строкой, разделенной запятой). Любая помощь будет принята с благодарностью.

Теги:
string
plsql
split
oracle11g
oracle10g
tokenize

14 ответов

98

Принятый ответ имеет низкую производительность при использовании больших наборов данных.

Это может быть улучшенным способом (также с регулярным выражением и соединением):

with temp as
(
    select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
    union all
    select 109, 'test2', 'Err1' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
order by name

РЕДАКТИРОВАТЬ: Вот простое (как в "не в глубине") объяснение запроса.

  1. length (regexp_replace(t.error, '[^,]+')) + 1 использует regexp_replace для удаления всего, что не является разделителем (в данном случае запятой), и length +1 для получения количества элементов (ошибок),
  2. Уровень select level from dual connect by level <= (...) использует иерархический запрос для создания столбца с растущим числом найденных совпадений, от 1 до общего количества ошибок.

    Предварительный просмотр:

    select level, length (regexp_replace('Err1, Err2, Err3', '[^,]+'))  + 1 as max 
    from dual connect by level <= length (regexp_replace('Err1, Err2, Err3', '[^,]+'))  + 1
    
  3. table(cast(multiset(.....) as sys.OdciNumberList)) выполняет некоторое приведение типов оракула.
    • Приведение cast(multiset(.....)) as sys.OdciNumberList преобразует несколько коллекций (по одной коллекции для каждой строки в исходном наборе данных) в одну коллекцию чисел OdciNumberList.
    • Функция table() преобразует коллекцию в набор результатов.
  4. FROM без объединения создает перекрестное соединение между вашим набором данных и мультимножеством. В результате строка в наборе данных с 4 совпадениями будет повторяться 4 раза (с возрастающим номером в столбце с именем "column_value").

    Предварительный просмотр:

    select * from 
    temp t,
    table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.error, '[^,]+'))  + 1) as sys.OdciNumberList)) levels
    
  5. trim(regexp_substr(t.error, '[^,]+', 1, levels.column_value)) использует column_value качестве параметра nth_appearance/ocurrence для regexp_substr.
  6. Вы можете добавить некоторые другие столбцы из вашего набора данных (t.name, t.project в качестве примера) для легкой визуализации.

Некоторые ссылки на документы Oracle:

  • 8
    отличное выступление
  • 6
    Осторожно! Регулярное выражение формата '[^,]+' для разбора строк не возвращает правильный элемент, если в списке есть нулевой элемент. Смотрите здесь для получения дополнительной информации: stackoverflow.com/questions/31464275/…
Показать ещё 3 комментария
28

регулярные выражения - замечательная вещь:)

with temp as  (
       select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
       union all
       select 109, 'test2', 'Err1' from dual
     )

SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str
  FROM (SELECT Name, Project, Error str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by Name
  • 1
    Привет, пожалуйста, объясните мне, почему вышеупомянутый запрос дает повторяющиеся строки, если я не использовал отдельное ключевое слово в запросе
  • 2
    Этот запрос неприменим из-за @JagadeeshG, особенно для больших таблиц.
Показать ещё 1 комментарий
25

Существует огромное различие между двумя ниже:

  • разделение отдельной строки с разделителями
  • разделение строк с разделителями для нескольких строк в таблице.

Если вы не ограничиваете строки, то предложение CONNECT BY создаст несколько строк и не даст желаемого результата.

Помимо Регулярных выражений, используется несколько других альтернатив:

  • XMLTable
  • МОДЕЛЬ.

Настройка

SQL> CREATE TABLE t (
  2    ID          NUMBER GENERATED ALWAYS AS IDENTITY,
  3    text        VARCHAR2(100)
  4  );

Table created.

SQL>
SQL> INSERT INTO t (text) VALUES ('word1, word2, word3');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word4, word5, word6');

1 row created.

SQL> INSERT INTO t (text) VALUES ('word7, word8, word9');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT * FROM t;

        ID TEXT
---------- ----------------------------------------------
         1 word1, word2, word3
         2 word4, word5, word6
         3 word7, word8, word9

SQL>

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

SQL> SELECT id,
  2         trim(COLUMN_VALUE) text
  3  FROM t,
  4    xmltable(('"'
  5    || REPLACE(text, ',', '","')
  6    || '"'))
  7  /

        ID TEXT
---------- ------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9

9 rows selected.

SQL>

Использование МОДЕЛЬ:

SQL> WITH
  2  model_param AS
  3     (
  4            SELECT id,
  5                      text AS orig_str ,
  6                   ','
  7                          || text
  8                          || ','                                 AS mod_str ,
  9                   1                                             AS start_pos ,
 10                   Length(text)                                   AS end_pos ,
 11                   (Length(text) - Length(Replace(text, ','))) + 1 AS element_count ,
 12                   0                                             AS element_no ,
 13                   ROWNUM                                        AS rn
 14            FROM   t )
 15     SELECT   id,
 16              trim(Substr(mod_str, start_pos, end_pos-start_pos)) text
 17     FROM     (
 18                     SELECT *
 19                     FROM   model_param MODEL PARTITION BY (id, rn, orig_str, mod_str)
 20                     DIMENSION BY (element_no)
 21                     MEASURES (start_pos, end_pos, element_count)
 22                     RULES ITERATE (2000)
 23                     UNTIL (ITERATION_NUMBER+1 = element_count[0])
 24                     ( start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
 25                     end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1) )
 26                 )
 27     WHERE    element_no != 0
 28     ORDER BY mod_str ,
 29           element_no
 30  /

        ID TEXT
---------- --------------------------------------------------
         1 word1
         1 word2
         1 word3
         2 word4
         2 word5
         2 word6
         3 word7
         3 word8
         3 word9

9 rows selected.

SQL>
  • 1
    Мне нравится трюк с xmltable ;-)
  • 1
    Можете ли вы уточнить, почему должно быть ('"' || REPLACE(text, ',', '","') || '"') и скобки не могут быть удалены? Документы Oracle ([ docs.oracle.com/database/121/SQLRF/functions268.htm ) мне не понятны. Это XQuery_string ?
Показать ещё 4 комментария
8

Несколько других примеров:

SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
  FROM dual
CONNECT BY LEVEL <= regexp_count('Err1, Err2, Err3', ',')+1
/

SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
  FROM dual
CONNECT BY LEVEL <= length('Err1, Err2, Err3') - length(REPLACE('Err1, Err2, Err3', ',', ''))+1
/

Кроме того, можно использовать DBMS_UTILITY.comma_to_table и table_to_comma: http://www.oracle-base.com/articles/9i/useful-procedures-and-functions-9i.php#DBMS_UTILITY.comma_to_table

  • 0
    Помните, что comma_to_table() работает только с токенами, которые соответствуют соглашениям об именах объектов базы данных Oracle. Например, он будет отбрасываться на строку типа '123,456,789' .
6

Я хотел бы предложить другой подход, используя функцию таблицы PIPELINED. Это несколько похоже на технику XMLTABLE, за исключением того, что вы предоставляете свою собственную настраиваемую функцию для разделения символьной строки:

-- Create a collection type to hold the results
CREATE OR REPLACE TYPE typ_str2tbl_nst AS TABLE OF VARCHAR2(30);
/

-- Split the string according to the specified delimiter
CREATE OR REPLACE FUNCTION str2tbl (
  p_string    VARCHAR2,
  p_delimiter CHAR DEFAULT ',' 
)
RETURN typ_str2tbl_nst PIPELINED
AS
  l_tmp VARCHAR2(32000) := p_string || p_delimiter;
  l_pos NUMBER;
BEGIN
  LOOP
    l_pos := INSTR( l_tmp, p_delimiter );
    EXIT WHEN NVL( l_pos, 0 ) = 0;
    PIPE ROW ( RTRIM( LTRIM( SUBSTR( l_tmp, 1, l_pos-1) ) ) );
    l_tmp := SUBSTR( l_tmp, l_pos+1 );
  END LOOP;
END str2tbl;
/

-- The problem solution
SELECT name, 
       project, 
       TRIM(COLUMN_VALUE) error
  FROM t, TABLE(str2tbl(error));

Результаты:

      NAME PROJECT    ERROR
---------- ---------- --------------------
       108 test       Err1
       108 test       Err2
       108 test       Err3
       109 test2      Err1

Проблема с этим типом подхода заключается в том, что оптимизатор часто не знает мощности функции таблицы, и он должен будет угадать. Это может быть потенциально опасным для ваших планов выполнения, поэтому это решение можно расширить, чтобы обеспечить статистику выполнения для оптимизатора.

Вы можете увидеть эту оценку оптимизатора, запустив EXPLAIN PLAN по указанному выше запросу:

Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806

----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         | 16336 |   366K|    59   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |         | 16336 |   366K|    59   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | T       |     2 |    42 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |  8168 | 16336 |    28   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Несмотря на то, что коллекция имеет только 3 значения, оптимизатор оценил 8168 строк для нее (значение по умолчанию). Вначале это может показаться неуместным, но оптимизатору может быть достаточно, чтобы принять решение о субоптимальном плане.

Решение состоит в том, чтобы использовать расширения оптимизатора для предоставления статистики для коллекции:

-- Create the optimizer interface to the str2tbl function
CREATE OR REPLACE TYPE typ_str2tbl_stats AS OBJECT (
  dummy NUMBER,

  STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
  RETURN NUMBER,

  STATIC FUNCTION ODCIStatsTableFunction ( p_function  IN  SYS.ODCIFuncInfo,
                                           p_stats     OUT SYS.ODCITabFuncStats,
                                           p_args      IN  SYS.ODCIArgDescList,
                                           p_string    IN  VARCHAR2,
                                           p_delimiter IN  CHAR DEFAULT ',' )
  RETURN NUMBER
);
/

-- Optimizer interface implementation
CREATE OR REPLACE TYPE BODY typ_str2tbl_stats
AS
  STATIC FUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )
  RETURN NUMBER
  AS
  BEGIN
    p_interfaces := SYS.ODCIObjectList ( SYS.ODCIObject ('SYS', 'ODCISTATS2') );
    RETURN ODCIConst.SUCCESS;
  END ODCIGetInterfaces;

  -- This function is responsible for returning the cardinality estimate
  STATIC FUNCTION ODCIStatsTableFunction ( p_function  IN  SYS.ODCIFuncInfo,
                                           p_stats     OUT SYS.ODCITabFuncStats,
                                           p_args      IN  SYS.ODCIArgDescList,
                                           p_string    IN  VARCHAR2,
                                           p_delimiter IN  CHAR DEFAULT ',' )
  RETURN NUMBER
  AS
  BEGIN
    -- I'm using basically half the string lenght as an estimator for its cardinality
    p_stats := SYS.ODCITabFuncStats( CEIL( LENGTH( p_string ) / 2 ) );
    RETURN ODCIConst.SUCCESS;
  END ODCIStatsTableFunction;

END;
/

-- Associate our optimizer extension with the PIPELINED function   
ASSOCIATE STATISTICS WITH FUNCTIONS str2tbl USING typ_str2tbl_stats;

Проверка итогового плана выполнения:

Execution Plan
----------------------------------------------------------
Plan hash value: 2402555806

----------------------------------------------------------------------------------------------
| Id  | Operation                          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |         |     1 |    23 |    59   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                      |         |     1 |    23 |    59   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | T       |     2 |    42 |     3   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH| STR2TBL |     1 |     2 |    28   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Как вы можете видеть, мощность на плане выше не является 8196 угаданными значение больше. Это все еще неверно, потому что мы передаем столбцу вместо строкового литерала в функцию.

Некоторая настройка на функциональный код будет необходима, чтобы дать более близкую оценку в этом конкретном случае, но я думаю, что общая концепция здесь в значительной степени объясняется.

Функция str2tbl, используемая в этом ответе, была первоначально разработана Томом Китом: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

Понятие об ассоциировании статистики с типами объектов можно дополнительно изучить, прочитав эту статью: http://www.oracle-developer.net/display.php?id=427

Описанная здесь методика работает в 10g +.

4

Я думаю, что лучший способ, которым я соединяюсь и регулярное выражение функции

   with temp as  (
       select 108 Name, 'test' Project, 'Err1, Err2, Err3' Error  from dual
       union all
       select 109, 'test2', 'Err1' from dual
     )

SELECT distinct Name, Project, trim(regexp_substr(str, '[^,]+', 1, level)) str
  FROM (SELECT Name, Project, Error str FROM temp) t
CONNECT BY instr(str, ',', 1, level - 1) > 0
order by Name

ИСТОЧНИК

  • 0
    Это не работает - sqlfiddle.com/#!4/9eecb7d/3457 это работало только для статической строки ...
4

REGEXP_COUNT не был добавлен до Oracle 11i. Здесь решение Oracle 10g, принятое из решения Art.

SELECT trim(regexp_substr('Err1, Err2, Err3', '[^,]+', 1, LEVEL)) str_2_tab
  FROM dual
CONNECT BY LEVEL <=
  LENGTH('Err1, Err2, Err3')
    - LENGTH(REPLACE('Err1, Err2, Err3', ',', ''))
    + 1;
  • 0
    Как я могу добавить фильтр для этого, скажем, я хочу фильтровать только с именем = '108'. Я попытался добавить оператор where после предложения from, но в итоге получился дубликат.
2

Без использования connect или regexp:

    with mytable as (
      select 108 name, 'test' project, 'Err1,Err2,Err3' error from dual
      union all
      select 109, 'test2', 'Err1' from dual
    )
    ,x as (
      select name
      ,project
      ,','||error||',' error
      from mytable
    )
    ,iter as (SELECT rownum AS pos
        FROM all_objects
    )
    select x.name,x.project
    ,SUBSTR(x.error
      ,INSTR(x.error, ',', 1, iter.pos) + 1
      ,INSTR(x.error, ',', 1, iter.pos + 1)-INSTR(x.error, ',', 1, iter.pos)-1
    ) error
    from x, iter
    where iter.pos < = (LENGTH(x.error) - LENGTH(REPLACE(x.error, ','))) - 1;
1

Начиная с Oracle 12c, вы можете использовать JSON_TABLE и JSON_ARRAY:

CREATE TABLE tab(Name, Project, Error) AS
SELECT 108,'test' ,'Err1, Err2, Err3' FROM dual UNION 
SELECT 109,'test2','Err1'             FROM dual;

И запрос:

SELECT *
FROM tab t
OUTER APPLY (SELECT TRIM(p) AS p
            FROM JSON_TABLE(REPLACE(JSON_ARRAY(t.Error), ',', '","'),
           '$[*]' COLUMNS (p VARCHAR2(4000) PATH '$'))) s;

Выход:

┌──────┬─────────┬──────────────────┬──────┐
│ Name │ Project │      Error       │  P   │
├──────┼─────────┼──────────────────┼──────┤
│  108 │ test    │ Err1, Err2, Err3 │ Err1 │
│  108 │ test    │ Err1, Err2, Err3 │ Err2 │
│  108 │ test    │ Err1, Err2, Err3 │ Err3 │
│  109 │ test2   │ Err1             │ Err1 │
└──────┴─────────┴──────────────────┴──────┘

db <> Fiddle demo

  • 1
    Я признаю, что это умный трюк, но, честно говоря, он озадачил бы меня, если бы я наткнулся на это в кодовой базе.
  • 0
    @APC Это просто демонстрация того, что возможно с SQL. Если бы мне пришлось использовать такой код в моей кодовой базе, я бы определенно обернул его в функцию или оставил расширенный комментарий :)
Показать ещё 1 комментарий
1

У меня была такая же проблема, и xmltable помог мне:

SELECT id, trim (COLUMN_VALUE) text FROM t, xmltable (('' '|| REPLACE (text,', ',' "," ') ||' "'))

1

Вот альтернативная реализация, использующая XMLTABLE, которая позволяет различать разные типы данных:

select 
  xmltab.txt
from xmltable(
  'for $text in tokenize("a,b,c", ",") return $text'
  columns 
    txt varchar2(4000) path '.'
) xmltab
;

... или если ваши строки с разделителями хранятся в одной или нескольких строках таблицы:

select 
  xmltab.txt
from (
  select 'a;b;c' inpt from dual union all
  select 'd;e;f' from dual
) base
inner join xmltable(
  'for $text in tokenize($input, ";") return $text'
  passing base.inpt as "input"
  columns 
    txt varchar2(4000) path '.'
) xmltab
  on 1=1
;
  • 0
    Я думаю, что это решение работает для Oracle 11.2.0.3 и более поздних версий.
1

Я хотел бы добавить еще один метод. В этом случае используются рекурсивные запросы, чего я не видел в других ответах. Он поддерживается Oracle с 11gR2.

with cte0 as (
    select phone_number x
    from hr.employees
), cte1(xstr,xrest,xremoved) as (
        select x, x, null
        from cte0
    union all        
        select xstr,
            case when instr(xrest,'.') = 0 then null else substr(xrest,instr(xrest,'.')+1) end,
            case when instr(xrest,'.') = 0 then xrest else substr(xrest,1,instr(xrest,'.') - 1) end
        from cte1
        where xrest is not null
)
select xstr, xremoved from cte1  
where xremoved is not null
order by xstr

Он довольно гибкий с характером расщепления. Просто измените его на вызовы INSTR.

0

я использовал функцию DBMS_UTILITY.comma_to _table, фактически ее работу код следующим образом

declare
l_tablen  BINARY_INTEGER;
l_tab     DBMS_UTILITY.uncl_array;
cursor cur is select * from qwer;
rec cur%rowtype;
begin
open cur;
loop
fetch cur into rec;
exit when cur%notfound;
DBMS_UTILITY.comma_to_table (
     list   => rec.val,
     tablen => l_tablen,
     tab    => l_tab);
FOR i IN 1 .. l_tablen LOOP
    DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
END LOOP;
end loop;
close cur;
end; 

Я использовал собственные имена таблиц и столбцов

  • 5
    Помните, что comma_to_table() работает только с токенами, которые соответствуют соглашениям об именах объектов базы данных Oracle. Например, он будет отбрасываться на строку типа '123,456,789' .
  • 0
    мы можем реализовать с использованием временных таблиц?
Показать ещё 1 комментарий
-2
CREATE FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString   varchar(1000))
RETURNS   @Result TABLE (Column1   VARCHAR(100))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(',',    @CommadelimitedString, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @CommadelimitedString, 0)      
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN 
END
GO

--Using the UDF to convert comma separated values into rows
SELECT * FROM dbo.BreakStringIntoRows('Apple,Banana,Orange')
SELECT * FROM dbo.BreakStringIntoRows('Apple   ,    Banana,    Orange')

Ещё вопросы

Сообщество Overcoder
Наверх
Меню