求写法,谢谢 比如一个tablename others小A yyy#mmm#dd怎么写可以使查询结果变成如下小A yyy小A mmm小A dd谢谢!!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你这个功能在java里面很好做的 --建表create table temp as SELECT '小A' NAME, 'yyy#mmm#dd' OTHERS FROM dual; --查询 WITH t1 AS (SELECT '小A' NAME, '#' || 'yyy#mmm#dd' OTHERS FROM temp)SELECT substr(t1.others, instr(t1.others, '#', 1, LEVEL) + 1, decode(instr(t1.others, '#', 1, LEVEL + 1), 0, length(OTHERS) + 1, instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s FROM dual, t1CONNECT BY LEVEL <= (SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1); --这个WITH t1 AS (SELECT NAME, '#' || OTHERS OTHERS FROM temp)SELECT DISTINCT NAME, substr(t1.others, instr(t1.others, '#', 1, LEVEL) + 1, decode(instr(t1.others, '#', 1, LEVEL + 1), 0, length(OTHERS) + 1, instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s FROM dual, t1CONNECT BY LEVEL <= (SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1 b WHERE b.name = t1.name)ORDER BY NAME; SQL> select * from ta; NAME OTHERS---- ----------小A yyy#mmm#dd小B zzz#nnn#ss SQL> SQL> select distinct name, 2 substr('#' || others || '#', 3 instr('#' || others || '#' , '#', 1, level) +1, 4 instr('#' || others || '#' , '#', 1, level + 1) - 5 instr('#' || others || '#' , '#', 1, level)-1) others 6 from ta 7 connect by level <= (length(others)-length(replace(others,'#')))/length('#') +1 8 order by name; NAME OTHERS---- ------------------------小A dd小A mmm小A yyy小B nnn小B ss小B zzz 6 rows selected PLSQL 查询乱码问题 两张表关联查询,求解,在线等 sql的like '%%' 如何理解。。。。。 求一sql文 急急急!!! c# 连接运行存储过程报这个错 ORA-01036: 非法的变量名/编号 报错,无效数字 请教Oracle的varchar2类型长度问题(急) 求SQL语句,表及测试数据如下 如何赋予用户只有向表A中插入数据的权限,其他都没有? 请问存储过程里goto语句如何使用? 求助:按内容输出查询结果的处理 急:vb连接oracle已成功,现在查询出错,请帮忙。
create table temp as
SELECT '小A' NAME, 'yyy#mmm#dd' OTHERS FROM dual;
--查询
WITH t1 AS
(SELECT '小A' NAME, '#' || 'yyy#mmm#dd' OTHERS FROM temp)
SELECT substr(t1.others,
instr(t1.others, '#', 1, LEVEL) + 1,
decode(instr(t1.others, '#', 1, LEVEL + 1),
0,
length(OTHERS) + 1,
instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s
FROM dual, t1
CONNECT BY LEVEL <= (SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1);
WITH t1 AS
(SELECT NAME, '#' || OTHERS OTHERS FROM temp)
SELECT DISTINCT NAME,
substr(t1.others,
instr(t1.others, '#', 1, LEVEL) + 1,
decode(instr(t1.others, '#', 1, LEVEL + 1),
0,
length(OTHERS) + 1,
instr(t1.others, '#', 1, LEVEL + 1)) - instr(t1.others, '#', 1, LEVEL) - 1) s
FROM dual, t1
CONNECT BY LEVEL <=
(SELECT length(OTHERS) - length(REPLACE(OTHERS, '#')) FROM t1 b WHERE b.name = t1.name)
ORDER BY NAME;
NAME OTHERS
---- ----------
小A yyy#mmm#dd
小B zzz#nnn#ss
SQL>
SQL> select distinct name,
2 substr('#' || others || '#',
3 instr('#' || others || '#' , '#', 1, level) +1,
4 instr('#' || others || '#' , '#', 1, level + 1) -
5 instr('#' || others || '#' , '#', 1, level)-1) others
6 from ta
7 connect by level <= (length(others)-length(replace(others,'#')))/length('#') +1
8 order by name;
NAME OTHERS
---- ------------------------
小A dd
小A mmm
小A yyy
小B nnn
小B ss
小B zzz
6 rows selected