用至少三种不同的写法实现逐字重复

solomon_007

with t  as (select rownum+64 n from dual connect by rownum<=3),
     t2 as (select replace(sys_connect_by_path(chr(n),','),',') str
              from t
             where level>=2
             start with n=65
           connect by nocycle n!=prior n )
select listagg(substr(a.str,b.column_value,1)||substr(a.str,b.column_value,1)) within group (order by b.column_value) res
   from (select rownum id,str from t2) a,table(cast(multiset(select rownum from dual connect by rownum <= length(a.str)) as sys.odcinumberlist)) b
group by a.id
order by a.id

lastwinner

真迅猛！方法早已不止三种，狂赞各位！
等待兔子、guostong、bell等人出招！

solomon_007

lastwinner 发表于 2012-3-5 02:37
真迅猛！方法早已不止三种，狂赞各位！
等待兔子、guostong、bell等人出招！

其实就是行复制的方法，至少6种，加上简单的函数与NEWKID的最简单的正则方法有至少8种了。。。

tree_new_bee

我承认我是个懒人。我很无赖地几乎照抄了野花的代码：

with t as (select rownum+64 n from dual connect by rownum<=3),
t2 as (select replace(sys_connect_by_path(chr(n),','),',') str,
replace(sys_connect_by_path(chr(n)||chr(n),','),',') str2
from t where level>=2 start with n=65 connect by nocycle n!=prior n )
select str,str2 from t2;

njlovebb

高手过招  真的不一样也··

newkid

tree_new_bee 发表于 2012-3-5 20:51
我承认我是个懒人。我很无赖地几乎照抄了野花的代码：

with t as (select rownum+64 n from dual connec ...

哈哈，果然是个无赖，把代码强插到人家的构造用例，非君子所为也！

newkid

xgghxkhuang 发表于 2012-3-4 10:24
select regexp_REPLACE('ABBCBABC','(.)','\1\1') FROM DUAL;
--1        AABBBBCCBBAABBCC
newkid的方法可行呀 ...

小数点匹配任意字符；加上括号的意思是把这个匹配当作一个GROUP并将在后面引用；而\1   \2   \3分别是对第1,2,3组的引用。

lastwinner

tree_new_bee 发表于 2012-3-5 20:51
我承认我是个懒人。我很无赖地几乎照抄了野花的代码：

with t as (select rownum+64 n from dual connec ...

我汗，你不能修改原始数据嘛……

xqmei

with t0 as ( select 'abc' str from dual)
select  xmlagg (xmlelement (e, str )).extract ('//text()') str from
  (select substr(str,x.num,1)||substr(str,x.num,1) str from t0,
  xmlTable( '1 to xs:integer($n)' passing length(t0.str) as "n" columns num for ordinality ) x )

newkid

xqmei 发表于 2012-3-6 03:47
with t0 as ( select 'abc' str from dual)
select  xmlagg (xmlelement (e, str )).extract ('//text()') ...

记得我以前就赞叹过你的XML技术，今天再赞一次！