【Oracle】ORA-01722: 数値が無効です の原因と解決方法完全ガイド｜暗黙変換・WHERE句・INSERT・JOINまで解説

-- 文字列 'ABC' を数値として扱おうとしている
SELECT TO_NUMBER('ABC') FROM dual;
-- → ORA-01722: 数値が無効です

-- product_code は VARCHAR2(20) 型
-- 大部分は数値だが、一部に 'N/A' や 'SPECIAL' が含まれている
SELECT * FROM products
WHERE product_code = 100;
-- → product_code 列全体に対して TO_NUMBER(product_code) が暗黙に実行される
-- → 'N/A' を数値変換しようとして ORA-01722

-- 文字列同士の比較にする → 型変換が発生しない
SELECT * FROM products
WHERE product_code = '100';
-- → 全行に対して文字列比較が行われるだけなのでエラーにならない

-- price 列は NUMBER 型
INSERT INTO products (product_id, name, price)
VALUES (1, 'テスト商品', '無料');
-- → '無料' を NUMBER に変換できない → ORA-01722

INSERT INTO products (product_id, name, price)
VALUES (1, 'テスト商品', 0);
-- → NUMBER 型の列に数値を挿入 → 問題なし

-- 移行元テーブルの列がVARCHAR2で、移行先がNUMBERの場合
INSERT INTO new_products (product_id, name, price)
SELECT
    product_id,
    name,
    TO_NUMBER(price_text)   -- 明示的に変換する
FROM old_products
WHERE VALIDATE_CONVERSION(price_text AS NUMBER) = 1;  -- 変換可能な行だけ

-- orders.customer_code は VARCHAR2(20)
-- customers.customer_id は NUMBER
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_code = c.customer_id;
-- → customer_code が暗黙にTO_NUMBERされる
-- → customer_code に 'GUEST' のような値があると ORA-01722

-- 方法1: NUMBER側を文字列に変換する（VARCHAR2列にインデックスがある場合に有利）
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_code = TO_CHAR(c.customer_id);

-- 方法2: VARCHAR2側を数値に変換し、変換できない行を除外する
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON TO_NUMBER(o.customer_code) = c.customer_id
WHERE REGEXP_LIKE(o.customer_code, '^[0-9]+$');

-- code列に '001', '002', 'N/A' が混在している
SELECT code, name FROM items
ORDER BY TO_NUMBER(code);
-- → 'N/A' の変換で ORA-01722

SELECT code, name FROM items
ORDER BY
    CASE WHEN REGEXP_LIKE(code, '^[0-9]+$') THEN TO_NUMBER(code) ELSE 999999 END,
    code;
-- 数値変換できない値は末尾に配置される

-- 整数のみ許容する場合
SELECT product_code, name
FROM products
WHERE NOT REGEXP_LIKE(product_code, '^[0-9]+$');
-- 結果: N/A, SPECIAL, (空文字列) 等が検出される

-- 小数・負数を含む数値を許容する場合
SELECT product_code, name
FROM products
WHERE NOT REGEXP_LIKE(product_code, '^-?[0-9]+(\.[0-9]+)?$');

-- VALIDATE_CONVERSION は変換可能なら 1、不可なら 0 を返す
SELECT
    product_code,
    name,
    VALIDATE_CONVERSION(product_code AS NUMBER) AS is_numeric
FROM products
WHERE VALIDATE_CONVERSION(product_code AS NUMBER) = 0;
-- → 数値に変換できない行だけ抽出される

SELECT
    product_code,
    TO_NUMBER(product_code DEFAULT 0 ON CONVERSION ERROR) AS numeric_code
FROM products;
-- 'N/A' → 0, '100' → 100, 'SPECIAL' → 0
-- ORA-01722 は発生しない

SELECT
    product_code,
    CASE
        WHEN REGEXP_LIKE(product_code, '^-?[0-9]+(\.[0-9]+)?$')
        THEN TO_NUMBER(product_code)
        ELSE NULL
    END AS numeric_code
FROM products;
-- 数値変換可能な場合のみ変換し、それ以外はNULLを返す