oracle d,c,b,a转换成a,b,c,d

shf675 2014-05-20 05:28:44

如题
oracle中有没可用的函数？

...全文

438 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

老包大人 2014-05-22

打赏
举报

引用 6 楼 justjustso 的回复:

[quote=引用 5 楼 wildwave 的回复:] [quote=引用 4 楼 justjustso 的回复:]

不能用wm_concat，这个函数不保证排序[/quote] 我原来以为wm_concat只要有order by也可以排序, 因为把ASC改成DESC后, 数据结果倒过来。 [/quote] 不过网上也有人说WM_CONCAT用窗口函数中的over可以排序, 同上, 少量数据测试没有问题, 但是不知道是否严谨.

SELECT MAX(N) N
  FROM (SELECT TO_CHAR(WM_CONCAT(N) OVER(ORDER BY NUM DESC)) N
          FROM (SELECT REGEXP_SUBSTR('张三,李四,王五', '[^(;|,)]+', 1, ROWNUM) N,ROWNUM NUM
                  FROM DUAL
                CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE('张三,李四,王五','[[:alnum:]]')) + 1))

老包大人 2014-05-22

打赏
举报

引用 5 楼 wildwave 的回复:

[quote=引用 4 楼 justjustso 的回复:]

不能用wm_concat，这个函数不保证排序[/quote] 我原来以为wm_concat只要有order by也可以排序, 因为把ASC改成DESC结果倒过来

SELECT TO_CHAR(WM_CONCAT(N)) 
    FROM (SELECT REGEXP_SUBSTR('张三,李四,王五', '[^(;|,)]+', 1, ROWNUM) N
            FROM DUAL
          CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE('张三,李四,王五', '[[:alnum:]]')) + 1
           ORDER BY ROWNUM ASC);--ORDER BY ROWNUM DESC[/color]

用下面的写法可能性能就不如上面的了

SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(N, ',')), 2) N
  FROM (SELECT REGEXP_SUBSTR('张三,李四,王五', '[^(;|,)]+', 1, ROWNUM) N,
               LENGTH(REGEXP_REPLACE('张三,李四,王五', '[[:alnum:]]')) + 2 -
               ROWNUM NUM
          FROM DUAL
        CONNECT BY ROWNUM <=LENGTH(REGEXP_REPLACE('张三,李四,王五', '[[:alnum:]]')) + 1
         ORDER BY NUM DESC)
 START WITH NUM = 1
CONNECT BY NUM = PRIOR NUM + 1

老包大人 2014-05-21

打赏
举报

引用 3 楼 wildwave 的回复:

create or replace function func_1(str in varchar2) return varchar2 as
  str_new varchar(4000);
begin
  for x in (select substr(str,
                          instr(',' || str, ',', 1, rownum),
                          instr(str || ',', ',', 1, rownum) -
                          instr(',' || str, ',', 1, rownum)) keyword
              from dual
            connect by rownum <= length(str) - length(replace(str, ',')) + 1
             order by keyword) loop
    str_new := str_new || ',' || x.keyword;
  end loop;
  return substr(str_new, 2);
end;

抄了三楼的函数, 稍微改了一点. CREATE FUNCTION FUNC_REVERSE_CN(STR IN VARCHAR2) RETURN VARCHAR2 AS STR_NEW VARCHAR(4000); BEGIN SELECT TO_CHAR(WM_CONCAT(N)) INTO STR_NEW FROM (SELECT REGEXP_SUBSTR(STR, '[^(;|,)]+', 1, ROWNUM) N FROM DUAL CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(STR, '[[:alnum:]]')) + 1 ORDER BY ROWNUM DESC); RETURN STR_NEW; END;

小灰狼W 2014-05-21

打赏
举报

create or replace function func_1(str in varchar2) return varchar2 as
  str_new varchar(4000);
begin
  for x in (select substr(str,
                          instr(',' || str, ',', 1, rownum),
                          instr(str || ',', ',', 1, rownum) -
                          instr(',' || str, ',', 1, rownum)) keyword
              from dual
            connect by rownum <= length(str) - length(replace(str, ',')) + 1
             order by keyword) loop
    str_new := str_new || ',' || x.keyword;
  end loop;
  return substr(str_new, 2);
end;

小灰狼W 2014-05-21