MySQLで列の値が文字列か数値かを判定して取得する方法

MySQLで列の値が文字列か数値かを判別する方法

MySQLでは、正規表現を使ったREGEXP演算子を利用することで、列の値が文字列なのか数値なのかを簡単に判別できます。

基本構文

文字列の値のみを取得したい場合は、以下の構文を使用します。

select *from yourTableName where yourColumnName NOT regexp '^[0-9]+$';

数値のみを取得したい場合は、以下の構文を使用します。

select *from yourTableName where yourColumnName regexp '^[0-9]+$';

ここで使われている正規表現 ^[0-9]+$ は、「先頭から末尾までがすべて0〜9の数字で構成されている」という意味です。このパターンに一致しない値（NOT regexp）を抽出すれば文字列のみ、一致する値を抽出すれば数値のみを取得できます。

実際の動作を確認する

まず、サンプル用のテーブルを作成しましょう。

mysql> create table DemoTable(
    Id varchar(100)
);
Query OK, 0 rows affected (0.49 sec)

次に、INSERTコマンドを使ってテーブルにいくつかのレコードを挿入します。数値だけの値と文字列を含む値を混ぜて登録します。

mysql> insert into DemoTable values('1000');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Carol_Smith');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('2000');
Query OK, 1 row affected (0.10 sec)

SELECT文を使って、テーブル内のすべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

以下のような出力が得られます。

+-------------+
| Id          |
+-------------+
| 1000        |
| John        |
| Carol_Smith |
| 2000        |
+-------------+
4 rows in set (0.00 sec)

ケース1：文字列の値のみを取得する

文字列の値だけを取得するには、以下のクエリを実行します。

mysql> select *from DemoTable where Id NOT regexp '^[0-9]+$';

実行結果として、文字列の列値のみが表示されます。

+-------------+
| Id          |
+-------------+
| John        |
| Carol_Smith |
+-------------+
2 rows in set (0.00 sec)

ケース2：数値のみを取得する

数値だけを取得するには、以下のクエリを実行します。

mysql> select *from DemoTable where Id regexp '^[0-9]+$';

実行結果として、数値の列値のみが表示されます。

+------+
| Id   |
+------+
| 1000 |
| 2000 |
+------+
2 rows in set (0.00 sec)

まとめ

REGEXP '^[0-9]+$' を使うことで、VARCHAR型の列に格納された値が純粋な数値か文字列かを判別できます。データクレンジングや型の整合性チェックなど、文字列と数値が混在するデータを扱う際に非常に便利なテクニックです。