oracle sql 字符串拆分的查询的问题,急!!!

发布网友发布时间：2小时前

共3个回答

热心网友时间：1小时前

SQL> create table a(t_name varchar2(10), t_desc varchar2(100));
Table created

SQL> insert into a(t_name, t_desc) values ('zhangsan','a,b,c');
1 row inserted

SQL> insert into a(t_name, t_desc) values ('lisi','b,c');
1 row inserted

SQL> insert into a(t_name, t_desc) values ('wangwu','c');
1 row inserted

SQL> insert into a(t_name, t_desc) values ('zhangliu','dd');
1 row inserted

SQL> select * from a;
T_NAME T_DESC
---------- --------------------------------------------------------------------------------
zhangsan a,b,c
lisi b,c
wangwu c
zhangliu dd

SQL>
SQL> select t.t_name, t_desc, regexp_substr(t_desc, '[^,]+', 1, column_value)
2 from a t,
3 table(cast(multiset
4 (select level
5 from dual
6 connect by level <= regexp_count(t.t_desc, ',') + 1) as
7 sys.odcivarchar2list));
T_NAME T_DESC REGEXP_SUBSTR(T_DESC,'[^,]+',1
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
zhangsan a,b,c a
zhangsan a,b,c b
zhangsan a,b,c c
lisi b,c b
lisi b,c c
wangwu c c
zhangliu dd dd
7 rows selected

热心网友时间：1小时前

你可以在拆分之前先全部在desc后面加逗号，然后按逗号拆分

热心网友时间：1小时前

desc内容全都是3个字符用2个的逗号分隔？