오라클에서 숫자 데이터가 포함되지 않은 행 찾기

오라클에서 숫자 데이터가 포함되지 않은 행 찾기

매우 큰 Oracle 테이블에서 문제가 있는 레코드를 찾고 있습니다.이 열에는 모든 숫자 데이터가 포함되어 있어야 합니다. 이 열은 varchar2 열이더라도 마찬가지입니다.숫자 데이터가 포함되지 않은 레코드를 찾아야 합니다(To_number(col_name) 함수가 이 열에서 호출하려고 하면 오류가 발생합니다).

저는 당신이 regexp_like 조건을 사용하고 정규 표현을 사용하여 숫자가 아닌 것을 찾을 수 있다고 생각했습니다.이게 도움이 됐으면 좋겠어요?!

SELECT * FROM table_with_column_to_search WHERE REGEXP_LIKE(varchar_col_with_non_numerics, '[^0-9]+');

표시기를 가져오는 방법

DECODE( TRANSLATE(your_number,' 0123456789',' ')

예.

SQL> select DECODE( TRANSLATE('12345zzz_not_numberee',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"contains char"

그리고.

SQL> select DECODE( TRANSLATE('12345',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"number"

그리고.

SQL> select DECODE( TRANSLATE('123405',' 0123456789',' '), NULL, 'number','contains char')
 2 from dual
 3 /

"number"

Oracle 11g에는 정규식이 있으므로 이를 사용하여 실제 숫자를 얻을 수 있습니다.

SQL> SELECT colA
  2  FROM t1
  3  WHERE REGEXP_LIKE(colA, '[[:digit:]]');

COL1
----------
47845
48543
12
...

'23g'과 같은 숫자가 아닌 값이 있으면 무시됩니다.

SGB의 답변과 달리, 저는 regexp를 통해 데이터의 실제 형식을 정의하고 이를 부정하는 것을 선호합니다.이를 통해 $DDD, DDD, DDD.DD와 같은 값을 정의할 수 있습니다. OP의 간단한 시나리오에서는 다음과 같습니다.

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^[0-9]+$');

모든 양의 정수가 아닌 정수를 찾습니다.음의 정수도 사용할 수 있다면 쉽게 변경할 수 있습니다. 선택적인 선행 마이너스만 추가하면 됩니다.

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+$');

부동 소수점을 수락하는 중...

SELECT * 
FROM table_with_column_to_search 
WHERE NOT REGEXP_LIKE(varchar_col_with_non_numerics, '^-?[0-9]+(\.[0-9]+)?$');

어떤 형식이든 마찬가지입니다.기본적으로 입력 데이터의 유효성을 검사할 수 있는 형식이 이미 있으므로 해당 형식과 일치하지 않는 데이터를 찾으려면...다른 형식을 제안하는 것보다 해당 형식을 부정하는 것이 더 간단합니다. SGB의 경우 양의 정수 이상을 원하는 경우에는 이를 수행하기가 다소 까다로울 수 있습니다.

사용

SELECT * 
FROM TableToSearch 
WHERE NOT REGEXP_LIKE(ColumnToSearch, '^-?[0-9]+(\.[0-9]+)?$');

몇 가지 테스트를 해본 결과, 이 해결책이 나왔으니 도움이 될 경우 알려주시기 바랍니다.

쿼리에 아래 2개 조건을 추가하면 숫자 데이터가 포함되지 않은 레코드를 찾을 수 있습니다.

 and REGEXP_LIKE(<column_name>, '\D') -- this selects non numeric data
 and not REGEXP_LIKE(column_name,'^[-]{1}\d{1}') -- this filters out negative(-) values

Oracle 12.2부터는 to_number 함수에 옵션이 있습니다.ON CONVERSION ERROR예외를 탐지하고 기본값을 제공할 수 있는 절입니다.

숫자 값의 검정에 사용할 수 있습니다.단순집합NULL변환이 실패하고 모든 null이 아닌 값을 파일링하는 경우.

예

with num as (
select '123' vc_col from dual union all
select '1,23'  from dual union all
select 'RV12P2000'  from dual union all
select null  from dual)
select
  vc_col 
from num
where /* filter numbers */
vc_col is not null and
to_number(vc_col DEFAULT NULL ON CONVERSION ERROR) is not null
;

VC_COL   
---------
123
1,23

출처: http://www.dba-oracle.com/t_isnumeric.htm

LENGTH(TRIM(TRANSLATE(, ' +-.0123456789', ' '))) is null

TRIM 후 문자열에 남아 있는 것이 있으면 숫자가 아닌 문자여야 합니다.

이것이 유용하다는 것을 알게 되었습니다.

 select translate('your string','_0123456789','_') from dual

결과가 NULL이면 숫자(부동점 번호 무시)입니다.

그런데 왜 밑줄이 필요한지 조금 당황스럽습니다.이 값이 없으면 다음도 null을 반환합니다.

 select translate('s123','0123456789', '') from dual

또한 제가 가장 좋아하는 트릭 중 하나가 있습니다. 문자열에 "*" 또는 "#"와 같은 것이 포함되어 있으면 완벽하지 않습니다.

 SELECT 'is a number' FROM dual WHERE UPPER('123') = LOWER('123')

몇 가지 테스트를 수행한 후, 이전 답변의 제안을 바탕으로 두 가지 사용 가능한 솔루션이 있는 것 같습니다.

방법 1이 더 빠르지만 더 복잡한 패턴을 일치시키는 측면에서는 덜 강력합니다.
방법 2는 더 유연하지만 더 느립니다.

1 - 가장 속도 1 - 가장 빠른 속도
저는 이 방법을 100만 줄의 테이블에서 테스트했습니다.
정규식 솔루션보다 3.8배 빠른 것 같습니다.
0 대체는 0이 공간에 매핑되어 쿼리 속도가 느려지지 않는 문제를 해결합니다.

SELECT *
FROM <table>
WHERE TRANSLATE(replace(<char_column>,'0',''),'0123456789',' ') IS NOT NULL;

2 - 이 더 높은 방법 2 - 방법 2
문 을 넣는 와 regex 문 에 넣는 둘 다 번역 솔루션보다 느립니다.결과적으로 정규식을 사용할 때 @ciuly의 접근 방식이 가장 현명해 보입니다.

SELECT *
FROM <table>
WHERE NOT REGEXP_LIKE(<char_column>, '^[0-9]+$');

다음 한 번만 선택할 수 있습니다.

create or replace function to_n(c varchar2) return number is
begin return to_number(c);
exception when others then return -123456;
end;

select id, n from t where to_n(n) = -123456;

저는 문제가 있는 열로 주문을 처리하고 열이 있는 행을 찾습니다.

SELECT 
 D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND,
          D.COL1  AS COL1


FROM
  VW_DATA_ALL_GC  D
  
  WHERE
  
   (D.PERIOADA IN (:pPERIOADA))  AND   
   (D.FORM = 62) 
   AND D.COL1 IS NOT NULL
 --  AND REGEXP_LIKE (D.COL1, '\[\[:alpha:\]\]')
 
-- AND REGEXP_LIKE(D.COL1, '\[\[:digit:\]\]')
 
 --AND REGEXP_LIKE(TO_CHAR(D.COL1), '\[^0-9\]+')
 
 
   GROUP BY 
    D.UNIT_CODE,
         D.CUATM,
         D.CAPITOL,
          D.RIND ,
          D.COL1  
         
         
        ORDER BY 
        D.COL1

언급URL : https://stackoverflow.com/questions/7957423/finding-rows-that-dont-contain-numeric-data-in-oracle