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

nannan5000

都是高手~~~

〇〇

居然忘记了最基本的写法，根据题意最多写20个substr
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 substr(str,1,1)||substr(str,1,1)||
substr(str,2,1)||substr(str,2,1)||
substr(str,3,1)||substr(str,3,1)||
substr(str,4,1)||substr(str,4,1)s
from t2;

solomon_007

SQL> with t  as (select rownum+64 n from dual connect by rownum<=3),
  2       t2 as (select replace(sys_connect_by_path(chr(n),','),',') str
  3                from t
  4               where level>=2
  5               start with n=65
  6             connect by nocycle n!=prior n )
  7  select listagg(substr(str,rn,1)||substr(str,rn,1)) within group(order by id,rn)
  8    from (select rownum id,str from t2) a,
  9         (select level rn from dual connect by level <=(select max(length(str)) from t2)) b
10   where substr(str,rn,1) is not null
11   group by id
12  /

LISTAGG(SUBSTR(STR,RN,1)||SUBS
--------------------------------------------------------------------------------
AABB
AABBCC
AACC
AACCBB

lastwinner

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

我是回的00的
你的疑问是正则的疑问，字符.代表的是很多字符，但并不是任意字符，\n他就代表不了

solomon_007

SQL> create or replace function f_str(p_str in varchar2)
  2  return varchar2
  3  is
  4    l_res varchar2(32767);
  5  begin
  6    for i in 1 .. length(p_str) loop
  7      l_res := l_res||substr(p_str,i,1)||substr(p_str,i,1);
  8    end loop;
  9    return l_res;
10  end;
11  /

Function created

SQL>
SQL> with t  as (select rownum+64 n from dual connect by rownum<=3),
  2       t2 as (select replace(sys_connect_by_path(chr(n),','),',') str
  3                from t
  4               where level>=2
  5               start with n=65
  6             connect by nocycle n!=prior n )
  7  select f_str(str) from t2
  8  /

F_STR(STR)
--------------------------------------------------------------------------------
AABB
AABBCC
AACC
AACCBB

lastwinner

〇〇 发表于 2012-3-4 12:51
居然忘记了最基本的写法，根据题意最多写20个substr
with t as (select rownum+64 n from dual connect by ...

真细致

regonly1

SQL> with tmp as(
  2  select 'AB' a from dual union all
  3  select 'ABC' a from dual union all
  4  select 'AC' a from dual union all
  5  select 'BC' a from dual)
  6  select a.a, replace(b.column_value,',') cv
  7    from tmp a, table(cast(multiset(select wmsys.wm_concat(substr(a.a, ceil(rownum/2),1)) from dual connect by rownum <= 2*length(a.a)) as sys.odcivarchar2list)) b
  8  /

A   CV
--- --------------------------------------------------------------------------------
AB  AABB
ABC AABBCC
AC  AACC
BC  BBCC

wkc168

with t as (select rownum+64 n from dual connect by rownum<=3),
tb 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 ),

  tb2 as
  (select level rn from dual connect by level<(select max(length(str))+1 from tb))
  select str,max(strres) strres
  from(
  select str,wm_concat(substr(str,rn,1)||substr(str,rn,1)) over(partition by str order by str,rn) strres
  from tb2,tb
    where length(str)>=rn
    )
group by str

solomon_007

SQL> with t  as (select rownum+64 n from dual connect by rownum<=3),
  2       t2 as (select replace(sys_connect_by_path(chr(n),','),',') str
  3                from t
  4               where level>=2
  5               start with n=65
  6             connect by nocycle n!=prior n )
  7  select listagg(dup) within group (order by rn) res
  8    from (
  9          select id,level rn,substr(str,level,1)||substr(str,level,1) dup
10            from (select rownum id ,str from t2)
11          connect by prior id = id and level <= length(str) and prior dbms_random.value is not null
12         )
13   group by id
14   order by id
15  /

RES
--------------------------------------------------------------------------------
AABB
AABBCC
AACC
AACCBB

solomon_007

SQL> with t  as (select rownum+64 n from dual connect by rownum<=3),
  2       t2 as (select replace(sys_connect_by_path(chr(n),','),',') str
  3                from t
  4               where level>=2
  5               start with n=65
  6             connect by nocycle n!=prior n )
  7  select replace(max(con),',',null) res
  8  from (
  9  select id,wm_concat(dup) over (partition by id order by rn) con
10    from (
11          select id,level rn,substr(str,level,1)||substr(str,level,1) dup
12            from (select rownum id ,str from t2)
13          connect by prior id = id and level <= length(str) and prior dbms_random.value is not null
14         )
15       )
16  group by id
17  order by id
18  /

RES
--------------------------------------------------------------------------------
AABB
AABBCC
AACC
AACCBB