--建一个函数把,一个sql很难写。 CREATE OR REPLACE FUNCTION F_SAME(I_COL VARCHAR2) RETURN VARCHAR2 AS V_COL VARCHAR2(4000); BEGIN SELECT WM_CONCAT(A1) INTO V_COL FROM (SELECT A1 FROM (SELECT REGEXP_SUBSTR(I_COL, '[^,]+', 1, LEVEL) AS A1 FROM DUAL CONNECT BY LEVEL <= LENGTH(I_COL) - LENGTH(REPLACE(I_COL, ',')) + 1) GROUP BY A1 HAVING COUNT(1) > 1 ORDER BY A1); RETURN V_COL; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN '无'; END; --测试[TEST@myorcl] SQL>SELECT F_SAME('1,2,3,4,5,2,3') from dual;F_SAME('1,2,3,4,5,2,3') -------------------------------------------------------------------------------- 2,3[TEST@myorcl] SQL>
我觉得 CONNECT BY LEVEL <= LENGTH(regexp_replace(i_col,',| ','')) 这样比较简单 同时 REGEXP_SUBSTR(I_COL, '[^, ]+', 1, LEVEL) AS A1 把空格也考虑进去,容错性好一些
给个经典的,我常用的哦,可是宝贝SQL,奉献给大家了,一句实现 SELECT COLNAME FROM (SELECT REGEXP_SUBSTR(TEXT, '[0-9]+', 1, RN) COLNAME FROM (SELECT '1,2,3,4,5,2,3' AS TEXT FROM DUAL) T1, (SELECT LEVEL RN FROM DUAL CONNECT BY ROWNUM <= (SELECT LENGTH(TEXT) - LENGTH(REPLACE(TEXT, ',', '')) + 1 FROM (SELECT '1,2,3,4,5,2,3' AS TEXT FROM DUAL))) T2) G GROUP BY COLNAME HAVING COUNT(*) > 1
--建一个函数把,一个sql很难写。
CREATE OR REPLACE FUNCTION F_SAME(I_COL VARCHAR2) RETURN VARCHAR2
AS
V_COL VARCHAR2(4000);
BEGIN
SELECT WM_CONCAT(A1)
INTO V_COL
FROM (SELECT A1
FROM (SELECT REGEXP_SUBSTR(I_COL, '[^,]+', 1, LEVEL) AS A1
FROM DUAL
CONNECT BY LEVEL <= LENGTH(I_COL) - LENGTH(REPLACE(I_COL, ',')) + 1)
GROUP BY A1
HAVING COUNT(1) > 1
ORDER BY A1);
RETURN V_COL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '无';
END;
--测试[TEST@myorcl] SQL>SELECT F_SAME('1,2,3,4,5,2,3') from dual;F_SAME('1,2,3,4,5,2,3')
--------------------------------------------------------------------------------
2,3[TEST@myorcl] SQL>
我觉得 CONNECT BY LEVEL <= LENGTH(regexp_replace(i_col,',| ','')) 这样比较简单
同时 REGEXP_SUBSTR(I_COL, '[^, ]+', 1, LEVEL) AS A1
把空格也考虑进去,容错性好一些
--这个建议很有道理,可以加上
REGEXP_SUBSTR(I_COL, '[^, ]+', 1, LEVEL) --但是下面这个建议就没有道理了
CONNECT BY LEVEL <= LENGTH(regexp_replace(i_col,',| ',''))
--因为level小于的应该是逗号的个数+1,你这么些虽然结果是对的但是如果字符串很长的话,会造成空的循环
--如:121,234,35445,1231
-----我的写法得到的是 level <= 4 你的写法得到的是 level<=15
SELECT COLNAME
FROM (SELECT REGEXP_SUBSTR(TEXT, '[0-9]+', 1, RN) COLNAME
FROM (SELECT '1,2,3,4,5,2,3' AS TEXT FROM DUAL) T1,
(SELECT LEVEL RN
FROM DUAL
CONNECT BY ROWNUM <=
(SELECT LENGTH(TEXT) -
LENGTH(REPLACE(TEXT, ',', '')) + 1
FROM (SELECT '1,2,3,4,5,2,3' AS TEXT FROM DUAL))) T2) G
GROUP BY COLNAME
HAVING COUNT(*) > 1