복붙노트

[SQL] 분할 쉼표 오라클 SQL 쿼리를 통해 행의 열 값을 분리

SQL

분할 쉼표 오라클 SQL 쿼리를 통해 행의 열 값을 분리

나는 다음과 같은 테이블이 있습니다 :

-------------
ID   | NAME
-------------
1001 | A,B,C
1002 | D,E,F
1003 | C,E,G
-------------

나는이 값으로 표시 할 :

-------------
ID   | NAME
-------------
1001 | A
1001 | B
1001 | C
1002 | D
1002 | E
1002 | F
1003 | C
1003 | E
1003 | G
-------------

나는 일을 시도 :

select split('A,B,C,D,E,F', ',') from dual; -- WILL RETURN COLLECTION

select column_value
from table (select split('A,B,C,D,E,F', ',') from dual); -- RETURN COLUMN_VALUE

해결법

  1. ==============================

    1.쿼리 아래 사용해보십시오 :

    쿼리 아래 사용해보십시오 :

     WITH T AS (SELECT 'A,B,C,D,E,F' STR  FROM DUAL)   SELECT    
 REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM T 
 CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T)

    ID와 쿼리 아래 :

    WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
)
SELECT    ID, 
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM TAB 
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM TAB);

    편집하다: 여러 ID와 다수의 분리를위한 쿼리 아래 사용해보십시오 :

    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
  2. ==============================

    2.여러 옵션이 있습니다. 참조 분할 쉼표는 오라클의 테이블에서 문자열을 구분.

    여러 옵션이 있습니다. 참조 분할 쉼표는 오라클의 테이블에서 문자열을 구분.

    REGEXP_SUBSTR 사용 :

    SQL> WITH sample_data AS(
  2  SELECT 10001 ID, 'A,B,C' str FROM dual UNION ALL
  3  SELECT 10002 ID, 'D,E,F' str FROM dual UNION ALL
  4  SELECT 10003 ID, 'C,E,G' str FROM dual
  5  )
  6  -- end of sample_data mimicking real table
  7  SELECT distinct id, trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  8  FROM sample_data
  9  CONNECT BY LEVEL <= regexp_count(str, ',')+1
 10  ORDER BY ID
 11  /

        ID STR
---------- -----
     10001 A
     10001 B
     10001 C
     10002 D
     10002 E
     10002 F
     10003 C
     10003 E
     10003 G

9 rows selected.

SQL>

    XMLTABLE 사용 :

    SQL> WITH sample_data AS(
  2  SELECT 10001 ID, 'A,B,C' str FROM dual UNION ALL
  3  SELECT 10002 ID, 'D,E,F' str FROM dual UNION ALL
  4  SELECT 10003 ID, 'C,E,G' str FROM dual
  5  )
  6  -- end of sample_data mimicking real table
  7  SELECT id,
  8        trim(COLUMN_VALUE) str
  9  FROM sample_data,
 10       xmltable(('"'
 11          || REPLACE(str, ',', '","')
 12          || '"'))
 13  /

        ID STR
---------- ---
     10001 A
     10001 B
     10001 C
     10002 D
     10002 E
     10002 F
     10003 C
     10003 E
     10003 G

9 rows selected.
  3. ==============================

    3.당신이 뭔가를 시도 할 수 있습니다 :

    당신이 뭔가를 시도 할 수 있습니다 :

    CREATE OR REPLACE TYPE "STR_TABLE"
as table of varchar2


create or replace function GetCollection( iStr varchar2, iSplit char default ',' ) return STR_TABLE as
pStr varchar2(4000) := trim(iStr);
rpart varchar(255);
pColl STR_TABLE := STR_TABLE();
begin
   while nvl(length(pStr),0) > 0 loop
         pos := inStr(pStr, iSplit );
         if pos > 0 then
            rpart := substr(pStr,1, pos-1);
            pStr  := substr(pStr,pos+1,length(pStr));
         else
            rpart := pStr;
            pStr := null;
         end if;
         if rpart is not null then
           pColl.Extend;
           pColl(pColl.Count) := rpart;
         end if;
   end loop;
   return pColl;
end;
  4. ==============================

    4.복잡한 쿼리에 데카르트 제품 결과 CONNECT BY 또는 정규 표현식을 사용하지 마십시오. 또한 위의 방법은 가능한 결과 (A, B, C, D, E, F)보다는 조합의 목록을 알고 기대

    복잡한 쿼리에 데카르트 제품 결과 CONNECT BY 또는 정규 표현식을 사용하지 마십시오. 또한 위의 방법은 가능한 결과 (A, B, C, D, E, F)보다는 조합의 목록을 알고 기대

    사용위한 XMLTable :

    SELECT c.fname, c.lname,
trim(COLUMN_VALUE) EMAIL_ADDRESS
 FROM 
  CONTACTS c, CONTACT_STATUS s,
  xmltable(('"'
  || REPLACE(EMAIL_ADDRESS, ';', '","')
  || '"'))
where  c.status = s.id

    COLUMN_VALUE는 XMLTABLE에 속하는 의사 열이다. 이 신속하고 정확하고 / O의 값을 알고 승 당신이 열을 참조 할 수 있습니다.

    이 열을 받아 값 "항목"의 표 "항목 2"가 "항목 3"가 자동으로 소스 테이블 (연락처)에 합류한다. 이것은 수천 개의 행에 테스트되었습니다

    메모 ';' XMLTABLE의 열 필드 세퍼레이터이다.

  5. ==============================

    5.나는 비슷한 문제가 이런 식으로 해결 ...

    나는 비슷한 문제가 이런 식으로 해결 ...

        select YT.ID,
           REPLACE(REGEXP_SUBSTR(','||YT.STR||',',',.*?,',1,lvl.lvl),',','') AS STR
    from YOURTABLE YT
    join (select level as lvl 
          from dual 
          connect by level <= (select max(regexp_count(STR,',')+1) from YOURTABLE)
         ) lvl on lvl.lvl <= regexp_count(YT.STR,',')+1
  6. ==============================

    6.나는 더 그랜드 쿠마 B의 솔루션을 시도하고 지금까지 일했다. 그러나 더 많은 데이터와 I가 성능 문제로 실행 (> 60 행> 7 레벨). 그러므로 나는 내가 대안으로 공유하고 싶습니다, 더 정적 변형을 사용했다.

    나는 더 그랜드 쿠마 B의 솔루션을 시도하고 지금까지 일했다. 그러나 더 많은 데이터와 I가 성능 문제로 실행 (> 60 행> 7 레벨). 그러므로 나는 내가 대안으로 공유하고 싶습니다, 더 정적 변형을 사용했다.

    WITH T AS (
      SELECT 1001 AS ID, 'A,B,C' AS NAME FROM DUAL
UNION SELECT 1002 AS ID, 'D,E,F' AS NAME FROM DUAL
UNION SELECT 1003 AS ID, 'C,E,G' AS NAME FROM DUAL
     )   --SELECT * FROM T
SELECT ID as ID,
       distinct_column AS NAME
  FROM ( SELECT t.ID,       
                trim(regexp_substr(t.NAME, '[^,]+', 1,1)) AS c1,
                trim(regexp_substr(t.NAME, '[^,]+', 1,2)) AS c2,
                trim(regexp_substr(t.NAME, '[^,]+', 1,3)) AS c3,
                trim(regexp_substr(t.NAME, '[^,]+', 1,4)) AS c4 -- etc.
           FROM T )
UNPIVOT ( distinct_column FOR cn IN ( c1, c2, c3, c4 ) )    


    ID NAME               
------ ------
  1001 A                    
  1001 B                    
  1001 C                    
  1002 D                    
  1002 E                    
  1002 F                    
  1003 C                    
  1003 E                    
  1003 G                    

9 Zeilen gewählt
  7. ==============================

    7.이 버전은 하나 개의 문자보다 긴 문자열로도 작동합니다 :

    이 버전은 하나 개의 문자보다 긴 문자열로도 작동합니다 :

    select regexp_substr('A,B,C,Karl-Heinz,D','[^,]+', 1, level) from dual
  connect by regexp_substr('A,B,C,Karl-Heinz,D', '[^,]+', 1, level) is not null;

    쉼표로 문자열을 분리 분할 선택 문의 IN 절에 전달하는 방법을 참조하십시오

    8. from https://stackoverflow.com/questions/23649813/split-comma-separated-values-of-a-column-in-row-through-oracle-sql-query by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] IN 문을 오라클 매개 변수?  (0) 2020.05.13
[SQL] 액세스 2013 SQL에 TRANSFORM 및 PIVOT  (0) 2020.05.13
[SQL] 세 이하의 문자와 단어에 대한 MySQL의 전체 텍스트 검색  (0) 2020.05.13
[SQL] 쉼표로 구분 된 목록 열에 별개의 행을 선택 MYSQL  (0) 2020.05.13
[SQL] PYODBC - 데이터 원본 이름을 찾을 수 없습니다 지정된 기본 드라이버가 없습니다  (0) 2020.05.13
,

티스토리툴바