为了你这个问题,我还真是想了很久,首先分享一个观点吧: oracle对正则支持很浅,建议不使用在oracle中使用正则表达式,因为容易造成low performance。由于你的条件比较多,我的sql也比较复杂,肯定我的有值得优化的地方,嘿嘿,新手路过。 WITH TS AS (SELECT '1111,222,3312' A FROM DUAL UNION ALL SELECT '1,2,3,4,5,6' FROM DUAL UNION ALL SELECT '66,77' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL UNION ALL SELECT '65,98,53' FROM DUAL ) SELECT DISTINCT a,regexp_substr(a,'[[:alnum:]]+',1,level) t,LEVEL e from ts connect by level<=length(regexp_replace(a,'[[:alnum:]]+'))+1
亲,都是我一个人在回复,我把sql丢给你吧,写了我半个小时--函数只是用于判断是否纯数字以及带符号的 CREATE OR REPLACE FUNCTION ONERE_TO_MANYRE(VALUE1 IN VARCHAR2,--这个是字符串 SYMBOL IN CHAR) RETURN VARCHAR2 IS--这个是你的判断字符,在你的需求里面是分号 RESULT VARCHAR2(100); STRING_NUM1 NUMBER; STRING_NUM2 NUMBER; Number_flag CHAR(1); reg_exp NVARCHAR2(100); BEGIN reg_exp := '^[1-9][0-9]*(' || SYMBOL || '[0-9]*)*'; SELECT COUNT(CASE LENGTH(REGEXP_SUBSTR(a, reg_exp)) WHEN LENGTH(A) THEN a END) B INTO Number_flag FROM (SELECT VALUE1 a FROM dual); IF Number_flag = 0 THEN RESULT := '存在非数字'; ELSIF Number_flag = 1 THEN
--这是使用正则表达式判断进行拆分(注:这个有缺陷) SELECT COUNT(*) INTO STRING_NUM2 FROM (sELECT DISTINCT regexp_substr(a, '[[:digit:]]+', 1, level) t from (SELECT VALUE1 a FROM DUAL) connect by level <= length(regexp_replace(a, '[[:digit:]]+'))) WHERE length(t) >= 20;
IF STRING_NUM2 >= 1 THEN RESULT := '存在超过20个字符的子字符'; ELSE RESULT := '有效字符'; END IF; END IF; /* --未使用正则表达式对源字符进行拆分并判断,不建议使用这个 SELECT COUNT(*) INTO STRING_NUM1 FROM (SELECT SUBSTR(SYMBOL || A.STR || SYMBOL, INSTR(SYMBOL || A.STR || SYMBOL, SYMBOL, 1, ROWNUM) + 1, INSTR(SYMBOL || A.STR || SYMBOL, SYMBOL, 1, ROWNUM + 1) - INSTR(SYMBOL || A.STR || SYMBOL, SYMBOL, 1, ROWNUM) - 1) T FROM (SELECT VALUE1 STR FROM DUAL) A, ALL_OBJECTS WHERE ROWNUM < LENGTH(SYMBOL || A.STR || SYMBOL) - LENGTH(REPLACE(SYMBOL || A.STR || SYMBOL, SYMBOL))) WHERE LENGTH(T) >= 20;
IF STRING_NUM1 >= 1 THEN RESULT := '存在超过20个字符的子字符'; END IF;*/ RETURN(RESULT); END ONERE_TO_MANYRE;
oracle对正则支持很浅,建议不使用在oracle中使用正则表达式,因为容易造成low performance。由于你的条件比较多,我的sql也比较复杂,肯定我的有值得优化的地方,嘿嘿,新手路过。 WITH TS AS
(SELECT '1111,222,3312' A FROM DUAL UNION ALL
SELECT '1,2,3,4,5,6' FROM DUAL UNION ALL
SELECT '66,77' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL UNION ALL
SELECT '65,98,53' FROM DUAL ) SELECT DISTINCT a,regexp_substr(a,'[[:alnum:]]+',1,level) t,LEVEL e
from ts
connect by level<=length(regexp_replace(a,'[[:alnum:]]+'))+1
实在不行,写oracle的function吧,这个真的不行,虽然实现目的,但是其性能,哎。
直接用实现不了,因为你每一段还有判断规则。。先按1楼的方法分割以后,再判断分割的时候存在不满足或者超过20的。。取count值即可,如果count为0即满足否者不满足。
CREATE OR REPLACE FUNCTION ONERE_TO_MANYRE(VALUE1 IN VARCHAR2,--这个是字符串
SYMBOL IN CHAR) RETURN VARCHAR2 IS--这个是你的判断字符,在你的需求里面是分号
RESULT VARCHAR2(100);
STRING_NUM1 NUMBER;
STRING_NUM2 NUMBER;
Number_flag CHAR(1);
reg_exp NVARCHAR2(100);
BEGIN
reg_exp := '^[1-9][0-9]*(' || SYMBOL || '[0-9]*)*';
SELECT COUNT(CASE LENGTH(REGEXP_SUBSTR(a, reg_exp))
WHEN LENGTH(A) THEN
a
END) B
INTO Number_flag
FROM (SELECT VALUE1 a FROM dual);
IF Number_flag = 0 THEN
RESULT := '存在非数字';
ELSIF Number_flag = 1 THEN
--这是使用正则表达式判断进行拆分(注:这个有缺陷)
SELECT COUNT(*)
INTO STRING_NUM2
FROM (sELECT DISTINCT regexp_substr(a, '[[:digit:]]+', 1, level) t
from (SELECT VALUE1 a FROM DUAL)
connect by level <=
length(regexp_replace(a, '[[:digit:]]+')))
WHERE length(t) >= 20;
IF STRING_NUM2 >= 1 THEN
RESULT := '存在超过20个字符的子字符';
ELSE
RESULT := '有效字符';
END IF;
END IF;
/* --未使用正则表达式对源字符进行拆分并判断,不建议使用这个
SELECT COUNT(*)
INTO STRING_NUM1
FROM (SELECT SUBSTR(SYMBOL || A.STR || SYMBOL,
INSTR(SYMBOL || A.STR || SYMBOL, SYMBOL, 1, ROWNUM) + 1,
INSTR(SYMBOL || A.STR || SYMBOL,
SYMBOL,
1,
ROWNUM + 1) -
INSTR(SYMBOL || A.STR || SYMBOL, SYMBOL, 1, ROWNUM) - 1) T
FROM (SELECT VALUE1 STR FROM DUAL) A, ALL_OBJECTS
WHERE ROWNUM <
LENGTH(SYMBOL || A.STR || SYMBOL) -
LENGTH(REPLACE(SYMBOL || A.STR || SYMBOL, SYMBOL)))
WHERE LENGTH(T) >= 20;
IF STRING_NUM1 >= 1 THEN
RESULT := '存在超过20个字符的子字符';
END IF;*/
RETURN(RESULT);
END ONERE_TO_MANYRE;