반응형
오라클에서 문자열을 여러 행으로 분할
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
스택에 대해 몇 가지 잠재적인 솔루션을 본 적이 있지만 하나의 열(쉼표로 구분된 문자열)에 불과합니다.어떤 도움이라도 주시면 감사하겠습니다.
이것은 개량된 방법일 수 있습니다(regexp 및 connect by에서도 마찬가지).
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, '[^,]+')) + 1regexp_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는 여러 수집(원래 데이터 세트의 각 행에 대해1개의 수집)을 단일 번호 집합인 OdciNumberList로 변환합니다.table()함수는 컬렉션을 결과 세트로 변환합니다.
FROM결합을 사용하지 않으면 데이터 세트와 멀티셋 간에 교차 결합이 생성됩니다.그 결과, 데이터 세트의 행이 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_valuen번째_incurance/ocurence 매개 변수로 지정됩니다.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 절에 의해 여러 행이 생성되어 원하는 출력이 제공되지 않습니다.
- 단일 구분 문자열의 경우 단일 쉼표로 구분된 문자열을 행으로 분할합니다.
- 테이블 내에서 구분된 문자열을 분할하려면 테이블 내에서 쉼표로 구분된 문자열 분할을 참조하십시오.
정규 표현 외에도 몇 가지 다른 방법이 사용되고 있습니다.
- XML 테이블
- 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>
XMLTAB 사용LE:
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_table을 사용할 수도 있습니다.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
이러한 접근 방식의 문제는 대부분의 경우 최적기가 테이블 함수의 카디널리티를 인식하지 못하고 추측을 해야 한다는 것입니다.이는 실행 계획에 잠재적으로 해가 될 수 있으므로 이 솔루션을 확장하여 옵티마이저의 실행 통계를 제공할 수 있습니다.
위의 쿼리에서 EXPLY 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개의 행(기본값)을 추정했습니다.이것은 처음에는 무관해 보일 수 있지만 최적기가 차선의 계획을 결정하는 것으로 충분할 수 있습니다.
해결책은 Optimizer 확장을 사용하여 수집 통계 정보를 제공하는 것입니다.
-- 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+로 동작합니다.
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 │
└──────┴─────────┴──────────────────┴──────┘
Oracle 11i까지 REGEXP_COUNT가 추가되지 않았습니다.다음은 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;
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
;
저도 같은 문제가 있었습니다.xmltable이 도움이 되었습니다.
SELECT id, trim(COLUMN_VALUE) 텍스트 FROM t, xmltable(')|| REPLACE(텍스트, '', '', '', | '''')
다른 방법을 추가하고 싶습니다.이건 재귀 쿼리를 사용하는데 다른 답변에서는 본 적이 없는 질문입니다.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콜을 클릭합니다.
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;
Oracle 11g 이상에서는 재귀 하위 쿼리 및 단순 문자열 함수(정규식 및 연관된 계층 하위 쿼리보다 빠를 수 있음)를 사용할 수 있습니다.
Oracle 셋업:
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
출력:
이름 | 프로젝트 | 오류---: | :------ | :----108 | test | Err1109 | test2 | Err1108 | test | Err2108 | test | Err3
db <>여기에 추가
Oracle APEX 5.1 이상이 설치되어 있는 경우 다음과 같은 편리한 기능을 사용할 수 있습니다.APEX_STRING.split기능. 예:
select q.Name, q.Project, s.column_value as Error
from mytable q,
APEX_STRING.split(q.Error, ',') s
두 번째 파라미터는 딜리미터 문자열입니다.또한 수행할 분할 수를 제한하기 위해 세 번째 매개 변수도 사용할 수 있습니다.
저는 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;
나는 내 테이블과 컬럼 이름을 사용했다.
언급URL : https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle
반응형
'programing' 카테고리의 다른 글
| 타이프스크립트에서 enum을 인덱스 키 유형으로 사용하는 방법 (0) | 2023.04.02 |
|---|---|
| 워드프레스에서 쿼리를 선택하다 (0) | 2023.04.02 |
| jQuery - 이벤트가 시작된 후 온클릭 이벤트 청취자를 일시적으로 비활성화하려면 어떻게 해야 합니까? (0) | 2023.04.02 |
| Linux에서의 워드프레스 개발 및 Windows 서버로의 전개 (0) | 2023.04.02 |
| TypeScript에서 속성을 가진 함수 개체를 만듭니다. (0) | 2023.04.02 |