SQL: null로 설정된_number로 변환할 수 없는 경우

SQL: null로 설정된_number로 변환할 수 없는 경우

다음과 같은 값을 가진 표가 있습니다.

ID      VALUE
-----------------------
23559   200
23562   -1 & {14376}#-1

번호 집합 NULL로 변환할 수 없는 경우 선택하고 싶습니다.

이것은 매우 이상한 코너 케이스이기 때문에 저는 일반적으로 번역기를 사용합니다.

SELECT 
  CASE
     WHEN NOT TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) IS NULL THEN NULL
     ELSE COLUMN_NAME
  END AS "NUMERIC_COLUMN"
FROM
  TABLE_NAME;

필요하다면 절차로 전환할 수도 있지만, 성과 측면에서 굉장히 많은 혜택이 있을지는 확신할 수 없습니다.

문자열을 숫자로 변환하고 예외를 잡는 함수를 만들 수 있습니다.뭐 이런 거.

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  BEGIN
    l_num := to_number( p_str );
  EXCEPTION
    WHEN others THEN
      l_num := null;
  END;

  RETURN l_num;
END;

그러면 가능합니다.

SELECT id, my_to_number( value )
  FROM your_table

REGEXP_를 사용할 수도 있습니다.좋아요:

SELECT id
     , CASE WHEN regexp_like(value,'^[0-9]+$') THEN TO_NUMBER(value)
            ELSE NULL
        END value
  FROM your_table;

예를 들어,

SQL> WITH q AS (
  2  SELECT 1 ID, '200' col FROM dual
  3  UNION
  4  SELECT 2, '-1 & {14376}#-1' FROM dual
  5  )
  6  SELECT id, CASE WHEN regexp_like(col,'^[0-9]+$') THEN TO_NUMBER(col) ELSE NULL END TEST FROM q;

        ID       TEST
---------- ----------
         1        200
         2 

Oracle 12.2를 사용하면 다음과 같은 기능을 사용하여 보다 쉽게 수행할 수 있습니다.on conversion error옵션:

select id, cast(value as number default null on conversion error) as value
from the_table;

선택적으로 형식 마스크를 지정할 수도 있습니다.to_number()기능.

저는 이것이 PL/SQL 함수를 사용하는 것보다 빠를 것이라고 추측합니다. 성능에 대해서는 확신할 수 없습니다.case등호를 달고하지만 그것은 확실히 훨씬 더 짧습니다.

CREATE OR REPLACE FUNCTION asnumber(p_val IN VARCHAR2) RETURN NUMBER IS
l_val NUMBER;
BEGIN
   l_val := TO_NUMBER(p_val);
   RETURN l_val;
EXCEPTION WHEN VALUE_ERROR THEN
   RETURN null;
END;

언급URL : https://stackoverflow.com/questions/6470819/sql-if-cannot-convert-to-number-set-as-null