PLSQL实现字符串排序

概述：

现在有一张表tt，表结构如下：

CREATE TABLE tt  (NAME VARCHAR2(20));
INSERT INTO tt VALUES('abcd');
INSERT INTO tt VALUES('dabc');
INSERT INTO tt VALUES('ewqa');
COMMIT;

需求1：

实现在name字段值的各字符之间加一个逗号，如abcd变成a,b,c,d 
实现：

SELECT rtrim(REGEXP_REPLACE(NAME, '(.)', '\1,'),',') FROM tt;

需求2：

实现需求1的同时还要实现各字符的排序，如dabc变成a,b,c,d 
实现：

WITH t AS (SELECT ROWNUM ID,NAME FROM tt),
--根据id解析字符串数组并排序
T_STR AS
(SELECT ID, SUBSTR(NAME, LEVEL, 1) NEWSTR
    FROM T
  CONNECT BY LEVEL <= LENGTH(NAME)
         AND PRIOR ID = ID
         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
   ORDER BY ID, NEWSTR)
--根据id将数据拼接
SELECT ID, LISTAGG(NEWSTR, ',') WITHIN GROUP(ORDER BY ID, NEWSTR)
  FROM T_STR
GROUP BY ID;

下面通过一函数来实现(11g)：

--创建测试表
CREATE TABLE tt  (NAME VARCHAR2(20));
INSERT INTO tt VALUES('abcd');
INSERT INTO tt VALUES('dabc');
INSERT INTO tt VALUES('ewqa');
COMMIT;

--创建一函数 fn_sortstr
CREATE OR REPLACE FUNCTION fn_sortstr(str IN varchar2) RETURN VARCHAR2
AS
l_return VARCHAR2(4000);
BEGIN
  WITH t AS (SELECT 1 ID,str FROM dual),
--根据id解析字符串数组并排序
 T_STR AS
(SELECT  ID, SUBSTR(str, LEVEL, 1) NEWSTR
    FROM t
  CONNECT BY LEVEL <= LENGTH(str)
         AND PRIOR ID = ID
         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
   ORDER BY ID, NEWSTR)
--根据id将数据拼接
SELECT LISTAGG(NEWSTR, ',')  WITHIN GROUP(ORDER BY ID, NEWSTR) INTO l_return

  FROM T_STR
GROUP BY ID;
RETURN l_return;
END;
--使用
SELECT fn_sortstr(NAME) FROM tt;

下面通过一函数来实现(10g)： 
–创建测试表

CREATE TABLE tt  (NAME VARCHAR2(20));
INSERT INTO tt VALUES('abcd');
INSERT INTO tt VALUES('dabc');
INSERT INTO tt VALUES('ewqa');
COMMIT;
--创建一函数 fn_sortstr
CREATE OR REPLACE FUNCTION fn_sortstr(str IN varchar2) RETURN VARCHAR2
AS
l_return VARCHAR2(4000);
BEGIN
  WITH t AS (SELECT 1 ID,str FROM dual),
  --根据id解析字符串数组并排序
 T_STR AS
(SELECT  ID, SUBSTR(str, LEVEL, 1) NEWSTR
    FROM t
  CONNECT BY LEVEL <= LENGTH(str)
         AND PRIOR ID = ID
         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
   ORDER BY ID, NEWSTR)
--根据id将数据拼接
SELECT wmsys.wm_concat(NEWSTR)   INTO l_return
  FROM T_STR
GROUP BY ID;
RETURN l_return;
END;
--使用
SELECT fn_sortstr(NAME) FROM tt;