오라클에서 여러 행으로 분할 문자열

나는이 PHP와 MySQL에 어느 정도 답을 알지만, 누군가가 나에게 오라클 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

그러나 나는 그들이 단지 (쉼표로 구분 된 문자열 인) 하나의 열을 차지, 스택 주위에 몇 가지 잠재적 인 솔루션을 보았다. 어떤 도움을 크게 감상 할 수있다.

해결법

==============================

1.이 (또한 의해 정규 표현식과 연결 포함) 개선 방법이 될 수 있습니다 :

이 (또한 의해 정규 표현식과 연결 포함) 개선 방법이 될 수 있습니다 :

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

편집하다: 다음은 간단한 쿼리의 설명 ( "깊이 없음"에서와 같이)입니다.

오라클 문서에 일부 참조 :

==============================

2.정규 표현식은 멋진 일입니다 :)

정규 표현식은 멋진 일입니다 :)

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

==============================

3.두 아래 사이에 큰 차이가있다 :

두 아래 사이에 큰 차이가있다 :

당신이 행을 제한하지 않는 경우, CONNECT BY 절은 여러 행을 생성 할 것이며, 원하는 출력을 제공하지 않습니다.

외에도 정규 표현식에서, 몇 가지 다른 대안이 사용된다 :

설정

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>

MODEL 절을 사용하여 :

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>

==============================

4.같은 더 많은 예제 몇 :

같은 더 많은 예제 몇 :

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

==============================

5.나는 파이프 라인 테이블 함수를 사용하여 다른 접근 방식을 제안하고 싶습니다. 그것은 당신이 문자열을 분할하는 사용자 정의 기능을 제공하는 것을 제외하고는 XMLTABLE의 기술 다소 비슷하다 :

나는 파이프 라인 테이블 함수를 사용하여 다른 접근 방식을 제안하고 싶습니다. 그것은 당신이 문자열을 분할하는 사용자 정의 기능을 제공하는 것을 제외하고는 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

접근의이 유형의 문제는 종종 최적화 프로그램이 테이블 함수의 중요도를 알 수 없습니다 그것은 추측을해야 할 것입니다. 이 솔루션은 최적화 프로그램 실행 통계를 제공하기 위해 확장 할 수 있도록이, 당신의 실행 계획에 potentialy 해가 될 수 있습니다.

당신은 쿼리 위의 계획을 EXPLAIN 실행하여이 최적화 추정치를 볼 수 있습니다

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 +에서 작동합니다.

==============================
6.REGEXP_COUNT 오라클 (11I)까지 추가 할 수 없습니다. 여기에 예술의 솔루션에서 채택 된 오라클 10g 솔루션입니다.

REGEXP_COUNT 오라클 (11I)까지 추가 할 수 없습니다. 여기에 예술의 솔루션에서 채택 된 오라클 10g 솔루션입니다.
```
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;
```

==============================

7.당신이 JSON_TABLE 및 JSON_ARRAY를 사용할 수 오라클 12C에 시작 :

당신이 JSON_TABLE 및 JSON_ARRAY를 사용할 수 오라클 12C에 시작 :

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 <> 바이올린 데모

==============================
8.나는 다른 방법을 추가하고 싶습니다. 이 사람은 재귀 쿼리, 내가 다른 답변에서 볼 수없는 무언가를 사용합니다. 그것은 11gR2부터 오라클에 의해 지원됩니다.

나는 다른 방법을 추가하고 싶습니다. 이 사람은 재귀 쿼리, 내가 다른 답변에서 볼 수없는 무언가를 사용합니다. 그것은 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 통화에서 변경.

==============================

9.또는 정규 표현식 연결 사용하지 않고 :

또는 정규 표현식 연결 사용하지 않고 :

    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;

==============================

10.여기에 다른 데이터 유형에 캐스팅 수 있습니다 XMLTABLE을 사용하여 대안 구현은 다음과 같습니다

여기에 다른 데이터 유형에 캐스팅 수 있습니다 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
;

==============================
11.저도 같은 문제가 있었는데, XMLTABLE은 나에게 도움이 :

저도 같은 문제가 있었는데, XMLTABLE은 나에게 도움이 :

SELECT ID, 트림 (COLUMN_VALUE) 텍스트 t FROM, XMLTABLE은 (( ' "'교체 || (텍스트, '', '', '') || '' '))

==============================

12.오라클 11g 이상에서는 (빨리 정규 표현식과 상관 계층 하위 쿼리보다 수 있습니다) 재귀 서브 쿼리 및 간단한 문자열 기능을 사용할 수 있습니다 :

오라클 11g 이상에서는 (빨리 정규 표현식과 상관 계층 하위 쿼리보다 수 있습니다) 재귀 서브 쿼리 및 간단한 문자열 기능을 사용할 수 있습니다 :

오라클 설정 :

CREATE TABLE table_name ( name, project, error ) as
 select 108, 'test',  'Err1, Err2, Err3' from dual union all
 select 109, 'test2', 'Err1'             from dual;

질문:

WITH table_name_error_bounds ( name, project, error, start_pos, end_pos ) AS (
  SELECT name,
         project,
         error,
         1,
         INSTR( error, ', ', 1 )
  FROM   table_name
UNION ALL
  SELECT name,
         project,
         error,
         end_pos + 2,
         INSTR( error, ', ', end_pos + 2 )
  FROM   table_name_error_bounds
  WHERE  end_pos > 0
)
SELECT name,
       project,
       CASE end_pos
       WHEN 0
       THEN SUBSTR( error, start_pos )
       ELSE SUBSTR( error, start_pos, end_pos - start_pos )
       END AS error
FROM   table_name_error_bounds

산출:

DB <> 바이올린 여기

==============================

13.나는 실제로 DBMS_UTILITY.comma_to _table 기능의 작동을 사용했다 코드는 다음과

나는 실제로 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;

나는 내 자신의 테이블 및 열 이름을 사용했다

from https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] SQL 서버에서 날짜의 제거 시간 부분에 가장 좋은 방법 (0)	2020.03.08
[SQL] ID 일치를 기반으로 한 테이블에서 다른 SQL 업데이트 (0)	2020.03.08
[SQL] SQL Server에서 INSERT 또는 UPDATE를위한 솔루션 (0)	2020.03.08
[SQL] 어떻게 SQL 주입에 대한 매개 변수화 된 쿼리 도움을? (0)	2020.03.07
[SQL] 어떻게 SQL SELECT에서 THEN ...는 IF를 수행합니까? (0)	2020.03.07

복붙노트

[SQL] 오라클에서 여러 행으로 분할 문자열

오라클에서 여러 행으로 분할 문자열

해결법

1.이 (또한 의해 정규 표현식과 연결 포함) 개선 방법이 될 수 있습니다 :

2.정규 표현식은 멋진 일입니다 :)

3.두 아래 사이에 큰 차이가있다 :

4.같은 더 많은 예제 몇 :

5.나는 파이프 라인 테이블 함수를 사용하여 다른 접근 방식을 제안하고 싶습니다. 그것은 당신이 문자열을 분할하는 사용자 정의 기능을 제공하는 것을 제외하고는 XMLTABLE의 기술 다소 비슷하다 :

6.REGEXP_COUNT 오라클 (11I)까지 추가 할 수 없습니다. 여기에 예술의 솔루션에서 채택 된 오라클 10g 솔루션입니다.

7.당신이 JSON_TABLE 및 JSON_ARRAY를 사용할 수 오라클 12C에 시작 :

8.나는 다른 방법을 추가하고 싶습니다. 이 사람은 재귀 쿼리, 내가 다른 답변에서 볼 수없는 무언가를 사용합니다. 그것은 11gR2부터 오라클에 의해 지원됩니다.

9.또는 정규 표현식 연결 사용하지 않고 :

10.여기에 다른 데이터 유형에 캐스팅 수 있습니다 XMLTABLE을 사용하여 대안 구현은 다음과 같습니다

11.저도 같은 문제가 있었는데, XMLTABLE은 나에게 도움이 :

12.오라클 11g 이상에서는 (빨리 정규 표현식과 상관 계층 하위 쿼리보다 수 있습니다) 재귀 서브 쿼리 및 간단한 문자열 기능을 사용할 수 있습니다 :

13.나는 실제로 DBMS_UTILITY.comma_to _table 기능의 작동을 사용했다 코드는 다음과

'SQL' 카테고리의 다른 글

티스토리툴바