合法的长度是一样的,所以简单+傻B的方法就是 select * from tablename where length(replace(date,'')) > 多少长度.
写个函数判断下不就可以了, select * from table_a where F_VALID_DATE(column_b)=0; 函数可以按照下面的这个写:CREATE OR REPLACE FUNCTION F_VALID_DATE(AVC_DATE IN VARCHAR2) RETURN NUMBER IS IN_LENGTH NUMBER; VC_DATE_FORMAT VARCHAR2(30); DT_DATE DATE; VC_SQL VARCHAR2(4000); BEGIN IN_LENGTH := LENGTH(AVC_DATE); VC_DATE_FORMAT := SUBSTR('yyyymmddhh24miss', 1, IN_LENGTH); VC_SQL := ' select to_date(' || AVC_DATE || ',''' || VC_DATE_FORMAT || ''') from dual'; EXECUTE IMMEDIATE VC_SQL INTO DT_DATE; RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END F_VALID_DATE;
函数有点问题少了几个特殊情况 ,修改下: CREATE OR REPLACE FUNCTION F_VALID_DATE(AVC_DATE IN VARCHAR2) RETURN NUMBER IS IN_LENGTH NUMBER; VC_DATE_FORMAT VARCHAR2(30); DT_DATE DATE; VC_SQL VARCHAR2(4000); BEGIN IN_LENGTH := LENGTH(AVC_DATE); IF IN_LENGTH > 14 THEN RETURN 0; END IF; IF IN_LENGTH >= 8 THEN IN_LENGTH := IN_LENGTH + 2; END IF; VC_DATE_FORMAT := SUBSTR('yyyymmddhh24miss', 1, IN_LENGTH); VC_SQL := ' select to_date(' || AVC_DATE || ',''' || VC_DATE_FORMAT || ''') from dual'; EXECUTE IMMEDIATE VC_SQL INTO DT_DATE; RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 0; END F_VALID_DATE;
select * from tablename where length(replace(date,'')) > 多少长度.
函数可以按照下面的这个写:CREATE OR REPLACE FUNCTION F_VALID_DATE(AVC_DATE IN VARCHAR2) RETURN NUMBER IS
IN_LENGTH NUMBER;
VC_DATE_FORMAT VARCHAR2(30);
DT_DATE DATE;
VC_SQL VARCHAR2(4000);
BEGIN
IN_LENGTH := LENGTH(AVC_DATE);
VC_DATE_FORMAT := SUBSTR('yyyymmddhh24miss', 1, IN_LENGTH);
VC_SQL := ' select to_date(' || AVC_DATE || ',''' ||
VC_DATE_FORMAT || ''') from dual';
EXECUTE IMMEDIATE VC_SQL
INTO DT_DATE;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END F_VALID_DATE;
函数有点问题少了几个特殊情况 ,修改下:
CREATE OR REPLACE FUNCTION F_VALID_DATE(AVC_DATE IN VARCHAR2) RETURN NUMBER IS
IN_LENGTH NUMBER;
VC_DATE_FORMAT VARCHAR2(30);
DT_DATE DATE;
VC_SQL VARCHAR2(4000);
BEGIN
IN_LENGTH := LENGTH(AVC_DATE);
IF IN_LENGTH > 14 THEN
RETURN 0;
END IF;
IF IN_LENGTH >= 8 THEN
IN_LENGTH := IN_LENGTH + 2;
END IF; VC_DATE_FORMAT := SUBSTR('yyyymmddhh24miss', 1, IN_LENGTH);
VC_SQL := ' select to_date(' || AVC_DATE || ',''' ||
VC_DATE_FORMAT || ''') from dual';
EXECUTE IMMEDIATE VC_SQL
INTO DT_DATE;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END F_VALID_DATE;