请教一个更新sql的写法！

diaokewei

with a as  
(select 'fbiuref384e3432<fjsduf>hgmdfig094<tgfe24f9>fn98awfmw' a from dual)
select to_char(replace(wm_concat(decode(str||r,'f0','X',str)), ','))
  from (select str,
               sum(decode(str, '<', 1, '>', -1, 0)) over(order by a range between unbounded preceding and current row) r
          from (select regexp_substr(a, '.', 1, level) str, level a
                  from a
                connect by level <= length(a)))

gliu12

diaokewei 发表于 2017-5-12 15:28
with a as  
(select 'fbiuref384e3432hgmdfig094fn98awfmw' a from dual)
select to_char(replace(wm_c ...

66666

xqmei

Model的写法：

with t0 as (
            select 'fbiuref384e3432<fjsduf>hgmdfig094<tgfe24f9>fn98awfmw' str from dual union all
                        select '<fjsduf>hgmdfig094<tgfe24f9>fn98awfmw' str from dual union all
                        select 'fbiuref384e3432<fjsduf>hgmdfig094<tgfe24f9>' str from dual union all
                        select '<fjsduf>hgmdfig094<tgfe24f9>' str from dual union all
                        select 'asdfasfd<saff>f><f<fd>' str from dual union all
                        select 'fbiuref384e3432<fjsduf>hgmdfig094<>fn98awfmw' str from dual                         
          )
select  str0, replace(replace(str,'f','X'),chr(10),'f') str1
  from  t0 model  partition by(row_number() over(order by 1) rn)
    dimension by(0 d)   measures(str str0, str,str str1)
    rules iterate (10000) until (str1[0] is null)
      (  str1[0] = regexp_substr(str[0],'\<[[:alnum:]]+\>',1,1),
             str[0]  = replace(str[0],str1[0],replace(str1[0],'f',chr(10)))
      )
/

newkid

xqmei 发表于 2017-5-13 04:08
Model的写法：

with t0 as (

把MODEL当循环用了，相当于一截一截取出来处理，不知道速度能否比得上PLSQL。

zxy877298415

SQL> select * from t;

ID
------------------------------------------------------------
fbiuref384e3432<fjsduf>hgmdfig094<tgfe24f9>fn98awfmw

SQL> select listagg(s2, '') within group(order by id) s3
  2    from (select id,
  3                 case
  4                   when instr(s1, '<') > 0 then
  5                    s1
  6                   else
  7                    replace(s1, 'f', 'X')
  8                 end s2
  9            from (select level as id, regexp_substr(s, '[^#]+', 1, level) s1
10                    from (select replace(replace(id, '>', '>#'), '<', '#<') s
11                            from t)
12                  connect by level <= regexp_count(s, '#') + 1));

S3
------------------------------------------------------------
XbiureX384e3432<fjsduf>hgmdXig094<tgfe24f9>Xn98awXmw