PLSQL实现字符串排序

来源：互联网发布：智商碾压知乎编辑：程序博客网时间：2024/05/19 18:15

概述：

现在有一张表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_STRGROUP 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_sortstrCREATE OR REPLACE FUNCTION fn_sortstr(str IN varchar2) RETURN VARCHAR2ASl_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_STRGROUP 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_sortstrCREATE OR REPLACE FUNCTION fn_sortstr(str IN varchar2) RETURN VARCHAR2ASl_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_STRGROUP BY ID;RETURN l_return;END;--使用SELECT fn_sortstr(NAME) FROM tt;

1 0