PLSQL实现字符串排序

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/jiaping0424/article/details/51392973

概述:

现在有一张表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;
阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页