"안전" TO_NUMBER()

"안전" TO_NUMBER()

SELECT TO_NUMBER('*') FROM DUAL

이것은 분명히 예외입니다.

ORA-01722: 유효하지 않은 번호

'건너뛰기'를 해서 그 '건너뛰기'를 할 수 있는 방법은 없을까?0또는NULL대신?

전체 문제:있습니다NVARCHAR2필드: 거의;-가 아닌 숫자를 포함합니다(예:*컬럼에서 가장 큰 숫자를 선택해야 합니다.

네, 형편없는 디자인인 건 알지만, 지금 제가 필요한 건...:-S

업데이트:

저는 이 문제를 해결했습니다.

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)

부터Oracle Database 12c Release 2TO_NUMBER를 사용하여DEFAULT ... ON CONVERSION ERROR:

SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

또는CAST:

SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;

db <> 데모 표시

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0) 

에는 스케일> 0(소수점 오른쪽 끝)의 숫자도 표시됩니다.

이것보다 더 좋은 것을 찾을 수 없었다.

function safe_to_number(p varchar2) return number is
    v number;
  begin
    v := to_number(p);
    return v;
  exception when others then return 0;
end;

select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;

123에서 123으로 변환되지만 123a 또는 123a3에서 0으로 변환됩니다.

원래 질문과 다소 오래된 스컬에 맞는

select a, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0)  from 
(
    select '1' a, 'not a number' b from dual
    union
    select '2' a, '1234' b from dual
)

번호를 테스트하기 위해 자신의 regexp를 굴리는 것은 다소 번거로운 일이지만, 아래의 코드가 유효할 수 있습니다.사용자 정의 기능이 포함된 Gabe의 다른 솔루션은 Oracle 내장 기능을 사용하고 있기 때문에 더 강력하다고 생각합니다(또한 제 regexp는 100% 정확하지 않을 수 있습니다). 하지만 시도해 볼 만한 가치가 있습니다.

with my_sample_data as (
  select '12345' as mynum from dual union all
  select '54-3' as mynum from dual union all
  select '123.4567' as mynum from dual union all
  select '.34567' as mynum from dual union all
  select '-0.3462' as mynum from dual union all
  select '0.34.62' as mynum from dual union all
  select '1243.64' as mynum from dual 
)
select 
  mynum, 
  case when regexp_like(mynum, '^-?\d+(\.\d+)?$') 
    then to_number(mynum) end as is_num
from my_sample_data

그러면 다음과 같은 출력이 생성됩니다.

MYNUM   IS_NUM
-------- ----------
12345   12345
54-3    
123.4567    123.4567
.34567  
-0.3462 -0.3462
0.34.62 
1243.64 1243.64

select DECODE(trim(TRANSLATE(replace(replace(A, ' '), ',', '.'), '0123456789.-', ' ')),
              null,
              DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '.', INSTR(replace(replace(A, ' '), ',', '.'), '.') + 1),
                     0,
                     DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '-', 2),
                            0,
                            TO_NUMBER(replace(replace(A, ' '), ',', '.'))))) A
  from (select '-1.1' A from DUAL union all select '-1-1' A from DUAL union all select ',1' A from DUAL union all select '1..1' A from DUAL) A;

이 코드에서는 -1-1, 1..1, 12-2 등의 문자열은 제외됩니다.그리고 여기서는 정규 표현을 쓰지 않았어요.

이전 솔루션(@sOliver 및 @Mike Meyers)을 조합하여 REGEXP에서 마지막 '$'를 삭제하여 가능한 한 많은 숫자를 얻으려고 합니다.

구성 테이블에서 실제 수를 필터링하고 숫자 옆에 '12일'이라는 "kind-of" 코멘트를 붙이기 위해 사용할 수 있습니다.

with my_sample_data as ( select '12345' as mynum from dual union all select '123.4567' as mynum from dual union all select '-0.3462' as mynum from dual union all select '.34567' as mynum from dual union all select '-.1234' as mynum from dual union all select '**' as mynum from dual union all select '0.34.62' as mynum from dual union all select '24Days' as mynum from dual union all select '42ab' as mynum from dual union all select '54-3' as mynum from dual ) SELECT mynum, COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num FROM my_sample_data;

줄 것이다

MYNUM    IS_NUM                                  
-------- ----------
12345    12345                                   
123.4567 123.4567                                
-0.3462  -0.3462                                 
.34567   0.34567                                 
-.1234   -0.1234                                 
**       0                                       
0.34.62  0.34                                    
24Days   24                                      
42ab     42                                      
54-3     54                                      

가장 좋은 방법은 기능 솔루션인 것 같습니다만, 저처럼 어려운 환경에서 필요한 권한이 없는 경우, 다음과 같이 시도해 보십시오.

SELECT
 CASE
  WHEN
     INSTR(TRANSLATE('123O0',
                     ' qwertyuıopğüasdfghjklşizxcvbnmöçQWERTYUIOPĞÜASDFGHJKLŞİZXCVBNMÖÇ~*\/(){}&%^#$<>;@€|:_=',
                     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
                     ),
       'X') > 0
  THEN 'Y'
  ELSE 'N'
END is_nonnumeric
FROM DUAL

참고로:제 경우는, 「」와 「」가 문제의 원인입니다.:) 그 점을 고려해 주십시오.이거에서 영감을 얻었어요.그리고 이게 더 간결해 보여요.

덧붙여서 2: Oracle 님, 이러한 작지만 귀중한 요구에 맞는 내장 기능을 만들어 주실 수 있습니까?

언급URL : https://stackoverflow.com/questions/4486949/safe-to-number