MySQLで文字列から英数字以外の文字をすべて削除する方法

MySQLでは、文字列に含まれる記号や特殊文字を取り除きたいケースがよくあります。ここでは、文字列から英数字（アルファベットと数字）以外の文字をすべて削除する方法を解説します。

削除対象となる英数字以外の文字の例

以下のような記号が該当します。

@, !, #, &, (), ?, /

組み込み関数は存在しない

残念ながら、MySQLには文字列から英数字以外の文字を削除するための組み込み関数は用意されていません。そのため、独自のユーザー定義関数を作成する必要があります。

以下は、RemoveNonAlphaNumericという関数の宣言と定義です。文字列を1文字ずつ走査し、正規表現パターン [[:alnum:]] に一致する英数字だけを連結して結果を返します。

mysql> delimiter //
mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC
   -> BEGIN
   ->   DECLARE var1, length SMALLINT DEFAULT 1;
   ->   DECLARE result CHAR(255) DEFAULT '';
   ->   DECLARE ch CHAR(1);
   ->   SET length = CHAR_LENGTH( s );
   ->   REPEAT
   ->     BEGIN
   ->       SET ch = MID( s, var1, 1 );
   ->       IF ch REGEXP '[[:alnum:]]' THEN
   ->         SET result = CONCAT(result, ch);
   ->       END IF;
   ->       SET var1 = var1 + 1;
   ->     END;
   ->   UNTIL var1 > length END REPEAT;
   ->   RETURN result;
   -> END //
Query OK, 0 rows affected (0.10 sec)

ユーザー定義関数の動作確認

作成した関数「RemoveNonAlphaNumeric」は、引数として渡された文字列からすべての英数字以外の文字を除去します。まずデリミタを元に戻し、実際に関数を呼び出して動作を確認してみましょう。

mysql> delimiter ;
mysql> SELECT 'My Email id is test@123!', RemoveNonAlphaNumeric('My Email id is test@123!');

実行結果

以下の出力を見ると、関数「RemoveNonAlphaNumeric」によって英数字以外の文字が正常に削除されたことがわかります。

+--------------------------+---------------------------------------------------+
| My Email id is test@123! | removeNonAlphaNumeric('My Email id is test@123!') |
+--------------------------+---------------------------------------------------+
| My Email id is test@123! | MyEmailidistest123                                |
+--------------------------+---------------------------------------------------+
1 row in set (0.15 sec)

結果の検証

処理後の文字列「MyEmailidistest123」には、元の文字列に含まれていた @ や ! の記号、また空白文字も残っていません。このことから、作成した関数が正しく動作していることが確認できます。

この手法を使えば、メールアドレスやユーザー入力データなどから不要な記号を一括で除去でき、データのクレンジングや整形に役立ちます。なお、マルチバイト文字（日本語など）を扱う場合は、文字コードや CHAR_LENGTH の挙動にも注意が必要です。