【SQL】指定した項目が数字以外のデータを抽出する方法｜REGEXP・ISNUMERIC・REGEXP_LIKEをRDBMS別に解説

この記事で学べること

• 「数字以外のデータ」とは何か（VARCHAR列の混在データ問題）
• MySQL：REGEXP / NOT REGEXP で数字以外を抽出する方法
• PostgreSQL：~ 演算子・SIMILAR TO で非数値を判定する方法
• SQL Server：TRY_CAST / ISNUMERIC / PATINDEX の使い分け
• Oracle：REGEXP_LIKE で非数値データを検出する方法
• 整数・小数・負数・指数表記に対応した正規表現パターン
• ISNUMERICの落とし穴と安全な代替手段
• NULL・空文字列の扱い方
• 数字のみ抽出する逆パターン
• RDBMS間の構文比較テーブル
• データクレンジング・インポートデータ検証への活用

注意：price列はVARCHAR型です。数値が入るべき列をVARCHAR型で定義してしまうと、数値以外の値が格納できてしまいます。赤字の行が「数字以外のデータ」として抽出される対象です。

ポイント：「数字以外」の定義はビジネス要件によって変わります。整数のみを数値とするか、小数・負数・指数表記も含めるかで、使う正規表現パターンが異なります。

SELECT *
FROM products
WHERE price NOT REGEXP ';

実行結果

+----+-------+-------+----------+
| id | name  | price | category |
+----+-------+-------+----------+
|  2 | 商品B | abc   | 飲料     |
|  4 | 商品D | N/A   | 日用品   |
|  6 | 商品F | ---   | 食品     |
|  7 | 商品G | 980.5 | 飲料     |
|  8 | 商品H | 未定  | 日用品   |
|  9 | 商品I | -500  | 食品     |
| 10 | 商品J |       | 飲料     |
| 12 | 商品L | 1.5e3 | 食品     |
+----+-------+-------+----------+

SELECT *
FROM products
WHERE price NOT REGEXP ';

実行結果

+----+-------+-------+----------+
| id | name  | price | category |
+----+-------+-------+----------+
|  2 | 商品B | abc   | 飲料     |
|  4 | 商品D | N/A   | 日用品   |
|  6 | 商品F | ---   | 食品     |
|  8 | 商品H | 未定  | 日用品   |
| 10 | 商品J |       | 飲料     |
| 12 | 商品L | 1.5e3 | 食品     |
+----+-------+-------+----------+

SELECT *
FROM products
WHERE NOT REGEXP_LIKE(price, ');

-- 整数以外を抽出
SELECT *
FROM products
WHERE price !~ ';

-- 小数・負数も数値とみなす
SELECT *
FROM products
WHERE price !~ ';

SELECT *
FROM products
WHERE price NOT SIMILAR TO ';

ポイント：SIMILAR TO では暗黙的に文字列全体がマッチ対象になるため、^ と $ は不要です。一方、~ 演算子では部分マッチなので ^ と $ で全体一致を指定する必要があります。

-- カスタム関数を作成
CREATE OR REPLACE FUNCTION is_numeric(text) RETURNS BOOLEAN AS $$
BEGIN
    PERFORM $1::NUMERIC;
    RETURN TRUE;
EXCEPTION WHEN others THEN
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 数値以外を抽出
SELECT *
FROM products
WHERE NOT is_numeric(price);

SELECT *
FROM products
WHERE TRY_CAST(price AS DECIMAL(18,2)) IS NULL
  AND price IS NOT NULL;

実行結果

+----+-------+-------+----------+
| id | name  | price | category |
+----+-------+-------+----------+
|  2 | 商品B | abc   | 飲料     |
|  4 | 商品D | N/A   | 日用品   |
|  6 | 商品F | ---   | 食品     |
|  8 | 商品H | 未定  | 日用品   |
| 10 | 商品J |       | 飲料     |
+----+-------+-------+----------+

SELECT *
FROM products
WHERE ISNUMERIC(price) = 0;

ISNUMERICの落とし穴：以下の値は ISNUMERIC = 1（数値と判定）になりますが、実際には数値として使えないことがあります。

-- 数字以外の文字を含むデータを抽出
SELECT *
FROM products
WHERE PATINDEX('%[^0-9]%', price) > 0;

SELECT *
FROM products
WHERE NOT REGEXP_LIKE(price, ');

-- 小数・負数に対応
SELECT *
FROM products
WHERE NOT REGEXP_LIKE(price, ');

-- 指数表記にも対応（完全版）
SELECT *
FROM products
WHERE NOT REGEXP_LIKE(price, ');

SELECT *
FROM products
WHERE TO_NUMBER(price DEFAULT NULL ON CONVERSION ERROR) IS NULL
  AND price IS NOT NULL;

ポイント：実務では「小数・負数を含む」パターン（^-?[0-9]+(\.[0-9]+)?$）が最も汎用的です。指数表記はビジネスデータでは稀なので、通常は不要です。

SELECT *
FROM products
WHERE price IS NULL
   OR price = ''
   OR price NOT REGEXP ';

実行結果

+----+-------+-------+----------+
| id | name  | price | category |
+----+-------+-------+----------+
|  2 | 商品B | abc   | 飲料     |
|  4 | 商品D | N/A   | 日用品   |
|  6 | 商品F | ---   | 食品     |
|  8 | 商品H | 未定  | 日用品   |
| 10 | 商品J |       | 飲料     |
| 11 | 商品K | NULL  | 日用品   |
| 12 | 商品L | 1.5e3 | 食品     |
+----+-------+-------+----------+

注意：Oracleでは空文字列がNULLとして扱われます。他のRDBMSでは空文字列とNULLは別物です。RDBMS間でクエリを移植する際は、この違いに注意してください。

-- MySQL
SELECT * FROM products
WHERE price REGEXP ';

-- PostgreSQL
SELECT * FROM products
WHERE price ~ ';

-- SQL Server
SELECT * FROM products
WHERE TRY_CAST(price AS DECIMAL(18,2)) IS NOT NULL;

-- Oracle
SELECT * FROM products
WHERE REGEXP_LIKE(price, ');

実行結果（MySQL）

+----+-------+-------+----------+
| id | name  | price | category |
+----+-------+-------+----------+
|  1 | 商品A | 1500  | 食品     |
|  3 | 商品C | 2400  | 食品     |
|  5 | 商品E | 3200  | 飲料     |
|  7 | 商品G | 980.5 | 飲料     |
|  9 | 商品I | -500  | 食品     |
+----+-------+-------+----------+

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN price REGEXP '
         THEN 1 ELSE 0 END) AS numeric_count,
    SUM(CASE WHEN price NOT REGEXP '
              OR price IS NULL
         THEN 1 ELSE 0 END) AS invalid_count
FROM products;

実行結果

+------------+---------------+---------------+
| total_rows | numeric_count | invalid_count |
+------------+---------------+---------------+
|         12 |             5 |             7 |
+------------+---------------+---------------+

-- 1. 不正データの特定
SELECT id, name, price,
    CASE
        WHEN price IS NULL THEN 'NULL値'
        WHEN price = '' THEN '空文字'
        WHEN price NOT REGEXP '
        THEN '非数値'
        ELSE 'OK'
    END AS status
FROM products;

-- 2. 不正データをデフォルト値に置換
UPDATE products
SET price = '0'
WHERE price IS NULL
   OR price = ''
   OR price NOT REGEXP ';

SELECT
    category,
    COUNT(*) AS total,
    SUM(CASE WHEN price NOT REGEXP '
              OR price IS NULL
         THEN 1 ELSE 0 END) AS invalid,
    ROUND(
        SUM(CASE WHEN price NOT REGEXP '
                  OR price IS NULL
             THEN 1 ELSE 0 END) * 100.0
        / COUNT(*), 1
    ) AS invalid_pct
FROM products
GROUP BY category;

実行結果

+----------+-------+---------+-------------+
| category | total | invalid | invalid_pct |
+----------+-------+---------+-------------+
| 食品     |     5 |       2 |        40.0 |
| 飲料     |     4 |       1 |        25.0 |
| 日用品   |     3 |       2 |        66.7 |
+----------+-------+---------+-------------+

-- MySQL
SELECT * FROM products
WHERE name NOT REGEXP ';

-- PostgreSQL
SELECT * FROM products
WHERE name !~ ';

-- SQL Server
SELECT * FROM products
WHERE PATINDEX('%[^a-zA-Z0-9]%', name) > 0;

-- Oracle
SELECT * FROM products
WHERE NOT REGEXP_LIKE(name, ');

-- スペースを除去してから判定
SELECT *
FROM products
WHERE TRIM(price) NOT REGEXP '
   OR TRIM(price) = ''
   OR price IS NULL;