请教如何用sql语句拆分记录？

Question

大家好，
   我现在有一个表：t_test,表结构：
id   name    items
1     aa      a,b c
2     aaa     c d,e
3     bbb     d,e，g我想把items中用“,”、“空格”、“全角，”隔开的字符串拆成单独的记录，用sql语句执行后想要这样的查询结果：
id   name   item
1    aa     a
1    aa     b
1    aa     c
2    aaa    c
2    aaa    d
2    aaa    e
3    bbb    d
3    bbb    e
3    bbb    g请问我如何用sql语句来处理，谢谢！

Answer 1

WITH tab AS(
SELECT 1 id,'aa' NAME,'a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual 
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT DISTINCT id,NAME,item FROM (
    SELECT id,NAME,SubStr(items,LEVEL,1) item
    FROM tab
    CONNECT BY LEVEL<=Length(items)
)
WHERE REGEXP_LIKE(item, '[[:alpha:]]' )结果：
ID   NAME  ITEM
------------------
1     aa     a
1     aa     b
1     aa     c
2     aaa    c
2     aaa    d
2     aaa    e
3     bbb    d
3     bbb    e
3     bbb    g

Answer 2

两点分析步骤，1先把item字段记录分拆了，然后在用oracle的层次查询，

Answer 3

先利用下面函数把表里数据整理下再拆分.
SQL> create or replace function f_convert(i_str in varchar2) return varchar2
  2  as
  3  char_now    char(1);
  4  new_str     varchar2(4000);
  5  begin
  6   new_str:='';
  7   for i in 1..length(i_str) loop
  8    char_now:=substr(i_str,i,1);
  9    if (char_now in (' ',',') or to_single_byte(char_now)!=char_now) then
 10       char_now:=' ';
 11    end if;
 12    if char_now!=' ' or (substr(new_str,-1,1)!=' ' and new_str is not null) then
 13       new_str:=new_str||char_now;
 14     end if;
 15   end loop;
 16   return new_str;
 17  end;
 18  /
 
Function created
 
SQL> select * from t_test;
 
        ID NAME                                                                             ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 aa                                                                               a,b c
         2 aaa                                                                              c d,e
         3 bb                                                                               d,e，g
 
SQL> select id,name,f_convert(items) items from t_test;
 
        ID NAME                                                                             ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 aa                                                                               a b c
         2 aaa                                                                              c d e
         3 bb                                                                               d e g
 
SQL>

Answer 4

SQL> create or replace function f_convert(i_str in varchar2) return varchar2
  2  as
  3  char_now    varchar2(10);
  4  new_str     varchar2(4000);
  5  begin
  6   new_str:='';
  7   for i in 1..length(i_str) loop
  8    char_now:=substr(i_str,i,1);
  9    if (char_now in (' ',',') or to_single_byte(char_now)!=char_now) then
 10       char_now:=' ';
 11    end if;
 12    if char_now!=' ' or (substr(new_str,-1,1)!=' ' and new_str is not null) then
 13       new_str:=new_str||char_now;
 14     end if;
 15   end loop;
 16   return trim(new_str);
 17  end;
 18  /
 
Function created
 
SQL> select * from t_test;
 
        ID NAME                                                                             ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 aa                                                                               a,b c
         2 aaa                                                                              c d,e
         3 bb                                                                               d,e，g
  
SQL> with a as(select id,name,' '||f_convert(items)||' ' items from t_test),
  2       b as(select rownum rn from dual connect by rownum<(select max(length(translate(items,' '||items,' ')))-1 from a))
  3  select id,name,substr(items,instr(items,' ',1,rn)+1,instr(items,' ',1,rn+1)-instr(items,' ',1,rn)-1) items
  4    from a,b;
 
        ID NAME                                                                             ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 aa                                                                               a
         1 aa                                                                               b
         1 aa                                                                               c
         2 aaa                                                                              c
         2 aaa                                                                              e
         2 aaa                                                                              d
         3 bb                                                                               d
         3 bb                                                                               g
         3 bb                                                                               e
 
9 rows selected
 
SQL> 

Answer 5

SQL> select * from t_test;
 
        ID NAME                                                                             ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 aa                                                                               aa,bb cc
         2 aaa                                                                              ccc ddd,eee
         3 bb                                                                               dds,efd，g
 
SQL> 
SQL> with a as(select id,name,' '||f_convert(items)||' ' items from t_test),
  2       b as(select rownum rn from dual connect by rownum<(select max(length(translate(items,' '||items,' ')))-1 from a))
  3  select id,name,substr(items,instr(items,' ',1,rn)+1,instr(items,' ',1,rn+1)-instr(items,' ',1,rn)-1) items
  4    from a,b ;
 
        ID NAME                                                                             ITEMS
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
         1 aa                                                                               aa
         1 aa                                                                               bb
         1 aa                                                                               cc
         2 aaa                                                                              ccc
         2 aaa                                                                              ddd
         2 aaa                                                                              eee
         3 bb                                                                               dds
         3 bb                                                                               efd
         3 bb                                                                               g
 
9 rows selected
 
SQL> 

Answer 6

按照楼主描述要求 
1.分割的是字符串，不是字符
2.看1#gelyon的SQL很精辟，但用了distinct，“a,a,a,b c”就有问题了
自己也试着写了个，可以试试。
WITH  T_TEST AS(
SELECT 1 ID,'name1' NAME,'str1,str2 str3' ITEMS FROM dual
UNION ALL
SELECT 2,'name2',' str4,str5,str6  ' FROM dual 
 UNION ALL
SELECT 3,'name3', 'str7，str8  ' FROM dual 
)
  select  ID ,NAME,regexp_substr(ITEMS,'\w+',1,level) 
    from   (select ID,NAME,trim(regexp_replace(ITEMS,'[, ，]+',' ')) ITEMS from T_TEST  )
    connect by   level<=length(regexp_replace(ITEMS,'\w+'))+1  and ID=connect_by_root(ID) 
  order by 1,2,3

Answer 7

with tb as(
select 1 id,'aa' NAME,'a,b c' items from dual
UNION ALL
select 2,'aaa','c d,e' from dual 
UNION ALL
select 3,'bbb', 'd,e，g' from dual
)
select *
from (select id,name,substr(items,level,1) items
from tb connect by level<=length(items) and connect_by_root(name)=name)
where regexp_instr(items,'[[:alpha:]]+',1)>0--结果        ID NAM IT
---------- --- --
         1 aa  a
         1 aa  b
         1 aa  c
         2 aaa c
         2 aaa d
         2 aaa e
         3 bbb d
         3 bbb e
         3 bbb g已选择9行。

Answer 8

谢谢提醒并指出缺点，改善：增加项次level就可以了：WITH tab AS(
SELECT 1 id,'aa' NAME,'a,a,b c' items FROM dual
UNION ALL
SELECT 2,'aaa','c d,e' FROM dual 
UNION ALL
SELECT 3,'bbb', 'd,e，g' FROM dual
)
SELECT id,NAME,item FROM (
    SELECT DISTINCT id,NAME,item ,levelno 
    FROM (
        SELECT id,NAME,SubStr(items,LEVEL,1) item ,LEVEL  levelno
        FROM tab
        CONNECT BY LEVEL<=Length(items)
    )
    WHERE REGEXP_LIKE(item, '[[:alpha:]]' )
)
ORDER  BY 1,2,3