반응형
Oracle에서 문자열을 여러 행으로 분할
나는 이것이 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
스택과 관련된 몇 가지 잠재적 솔루션을 보았지만 단일 열 (쉼표로 구분 된 문자열) 만 설명했습니다. 어떤 도움이라도 대단히 감사하겠습니다.
이것은 개선 된 방법 일 수 있습니다 (정규식 및 연결 방법도 사용) :
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
편집 : 다음은 쿼리에 대한 간단한 설명입니다 ( "깊이가 아님").
length (regexp_replace(t.error, '[^,]+')) + 1용도regexp_replace구분 기호 (이 경우 쉼표)가 아닌 것은 삭제하고length +1많은 요소 (오류)가 얼마나 얻을.는
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', '[^,]+')) + 1table(cast(multiset(.....) as sys.OdciNumberList))오라클 유형의 일부 캐스팅을 수행합니다.cast(multiset(.....)) as sys.OdciNumberList번호 단일 컬렉션 OdciNumberList으로 변형 여러 모음 (원래의 데이터 세트의 각 행에 대해 하나 개의 컬렉션).- 이
table()함수는 컬렉션을 결과 집합으로 변환합니다.
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)) levelstrim(regexp_substr(t.error, '[^,]+', 1, levels.column_value))에column_value대한 nth_appearance / ocurrence 매개 변수로를 사용regexp_substr합니다.t.name, t.project쉬운 시각화를 위해 데이터 세트에서 다른 열을 추가 할 수 있습니다 ( 예 : 예).
Oracle 문서에 대한 일부 참조 :
정규 표현식은 멋진 것입니다 :)
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
아래 두 가지 사이에는 큰 차이가 있습니다.
- 구분 된 단일 문자열 분할
- 테이블의 여러 행에 대해 구분 된 문자열을 분할합니다.
행을 제한하지 않으면 CONNECT BY 절이 여러 행을 생성 하고 원하는 출력을 제공하지 않습니다.
- 단일 구분 문자열의 경우 단일 쉼표로 구분 된 문자열을 행으로 분할을 참조하세요.
- 테이블에서 구분 된 문자열 을 분할하려면 테이블에서 쉼표로 구분 된 문자열 분할을 참조하세요.
정규 표현식 외에도 몇 가지 다른 대안이 사용됩니다.
- XMLTable
- MODEL 절
설정
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>
동일한 몇 가지 예 :
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
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 함수는 원래 Tom Kyte가 개발했습니다. 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 +에서 작동합니다.
REGEXP_COUNT는 Oracle 11i까지 추가되지 않았습니다. 다음은 Art의 솔루션에서 채택한 Oracle 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;
내가 연결하는 가장 좋은 방법과 regexp 함수
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
connect by 또는 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;
다음은 다른 데이터 유형으로 캐스트 할 수있는 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
;
오라클 당신이 사용할 수있는 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 │
└──────┴─────────┴──────────────────┴──────┘
다른 방법을 추가하고 싶습니다. 이것은 재귀 쿼리를 사용하며 다른 답변에서는 보지 못했습니다. 11gR2부터 Oracle에서 지원합니다.
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통화 에서 간단히 변경하십시오 .
I had the same problem, and xmltable helped me:
SELECT id, trim(COLUMN_VALUE) text FROM t, xmltable(('"' || REPLACE(text, ',', '","') || '"'))
i had used the DBMS_UTILITY.comma_to _table function actually its working the code as follows
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;
i had used my own table and column names
참고URL : https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle
반응형
'code' 카테고리의 다른 글
| 누구든지 여전히 C #에서 [goto]를 사용합니까? 그렇다면 그 이유는 무엇입니까? (0) | 2020.08.25 |
|---|---|
| Django- "django.core.management라는 모듈 없음" (0) | 2020.08.25 |
| PowerShell을 사용하여 파일에서 ReadOnly 특성을 제거하는 방법? (0) | 2020.08.25 |
| isset () 및 empty ()를 피하는 방법 (0) | 2020.08.25 |
| 데이터베이스 테이블의 열에 목록을 저장하는 방법 (0) | 2020.08.25 |