Oracle에서 쉼표로 구분된 값을 열로 분할

Oracle에서 쉼표로 구분된 값을 열로 분할

255개의 쉼표로 구분된 값으로 반환되는 값이 있습니다.255개의 기판 없이 그것들을 열로 나누는 쉬운 방법이 있습니까?

ROW  | VAL
----------- 
1    | 1.25, 3.87, 2, ... 
2    | 5, 4, 3.3, ....

로.

ROW | VAL | VAL | VAL ...
--------------------- 
1   |1.25 |3.87 | 2 ...     
2   | 5   | 4   | 3.3 ...

주의! 형식의 regexp_substr 표현식'[^,]+'목록에 null 요소가 있고 해당 항목 또는 항목 뒤에 하나를 지정하려는 경우에는 예상 값을 반환하지 않습니다.4번째 요소가 NULL이고 5번째 요소를 원하므로 '5'가 반환될 것으로 예상되는 이 예를 생각해 보십시오.

SQL> select regexp_substr('1,2,3,,5,6', '[^,]+', 1, 5) from dual;

R
-
6

놀랐지!실제 5번째 요소가 아닌 5번째 NON-NULL 요소를 반환합니다!잘못된 데이터가 반환되어 이를 파악하지 못할 수도 있습니다.대신 사용해 보십시오.

SQL> select regexp_substr('1,2,3,,5,6', '(.*?)(,|$)', 1, 5, NULL, 1) from dual;

R
-
5

따라서 위에서 수정한 REGEXP_SUBSTR은 쉼표 또는 줄의 끝(다음 구분 기호, 쉼표 또는 줄의 끝 모두 허용) 뒤에 0개 이상의 쉼표로 구분된 문자가 5번째로 나타나는지 확인하고 찾으면 첫 번째 하위 그룹(줄의 쉼표 또는 끝을 포함하지 않는 데이터)을 반환합니다.

검색 일치 패턴'(.*?)(,|$)'설명:

(             = Start a group
.             = match any character
*             = 0 or more matches of the preceding character
?             = Match 0 or 1 occurrences of the preceding pattern
)             = End the 1st group
(             = Start a new group (also used for logical OR)
,             = comma
|             = OR
$             = End of the line
)             = End the 2nd group

편집: 추가 정보를 추가하고 정규식을 단순화했습니다.

쉽게 재사용할 수 있도록 함수에 캡슐화하기 위한 자세한 내용과 제안은 이 게시물을 참조하십시오. REGEX는 목록에서 n번째 값을 선택하여 null을 허용합니다. 형식을 발견한 게시물입니다.'[^,]+'문제가 있습니다.유감스럽게도 이것은 목록을 구문 분석하는 방법과 관련된 질문에 대한 답변으로 가장 일반적으로 볼 수 있는 정규식 형식입니다.에 의해 반환되는 모든 잘못된 데이터를 생각하면 몸서리가 쳐집니다.'[^,]+'!

사용할 수 있습니다.regexp_substr():

select regexp_substr(val, '[^,]+', 1, 1) as val1, 
       regexp_substr(val, '[^,]+', 1, 2) as val2, 
       regexp_substr(val, '[^,]+', 1, 3) as val3, 
       . . .

Excel(또는 다른 스프레드시트)에서 255개 숫자 열을 생성하고 스프레드시트를 사용하여 SQL 코드를 생성하는 것이 좋습니다.

행이 하나만 있고 다음을 만들 시간이 있는 경우

• 기본 제공 기능 생성cto_table 임의의 구분 기호에서 문자열을 분할하는 기능, 그러면 사용할 수 있습니다.PIVOT + LISTAGG다음과 같이 수행합니다.

---

select * from (
  select rownum r , collection.*  
    from TABLE(cto_table(',','1.25, 3.87, 2, 19,, 1, 9, ')) collection
)
PIVOT ( 
  LISTAGG(column_value) within group (order by 1) as val 
  for r in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
)

---

참고로 다음은 다음과 같은 방법입니다.cto_table함수:

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END cto_table;
/

계층 쿼리를 사용할 수 있습니다.피벗은 대/소문자를 사용하여 수행할 수 있습니다.

with value_t as
( 
  select row_t,row_number() OVER (partition by row_t order by rownum )rn,
  regexp_substr(val, '[^,]+', 1, LEVEL) val from Table1
CONNECT BY LEVEL <= regexp_count(val, '[^,]+') 
AND prior row_t = row_t 
AND prior sys_guid() is not null
  ) select row_t, max( case when rn = 1 THEN val end ) val_1,
  max( case when rn = 2 THEN val end ) val_2,
  max( case when rn = 3 THEN val end ) val_3
  from value_t
  group by row_t;

언급URL : https://stackoverflow.com/questions/31464275/split-comma-separated-values-to-columns-in-oracle