英数字以外が含まれている値を抽出する

英数字以外が含まれている値を抽出するには？

データベースに保存されている値に、想定していなかった文字が入ってしまって、プログラムが期待通りに動かない！ なんていう時ありませんか？

今回は、半角の英数字しか想定していなかったカラムに、改行文字や記号が入っていたりするような状況で、英数字以外が含まれている値を抽出するのに便利なクエリーをご紹介します。

英数字以外が含まれている値のみを抽出するクエリー

例えば次のような Branch テーブルがあったとします。

CREATE TABLE Branch (
	BranchID	INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
	BranchCode	VARCHAR(5) NOT NULL,
	BranchName	NVARCHAR(100) NULL
);

BranchCode は英数字で入力して欲しかったのですが、プログラムでバリデーションを入れていなかったので、ユーザーが次のような値を入れてしまったとします。

INSERT INTO Branch 
	(BranchCode, BranchName)
VALUES 
	('CAL1', N'カリフォルニア第一支店'),
	('CAL 2', N'カリフォルニア第二支店'),	
	('LVS', N'ラスベガス支店'),
	('SF#', N'サンフランシスコ支店'),
	('NYC' + CHAR(13) + CHAR(10), N'ニューヨーク支店'),
	('NYC2', N'ニューヨーク第二支店');

英数字以外が含まれているデータを抽出し、BranchCode を英数字のみ変更したい！

そんな時にはこんなクエリーで抽出することができます。

SELECT	*
FROM	Branch
WHERE	BranchCode LIKE '%[^0-9A-Za-z]%'

LIKE オペレーターの [^] ワイルドカードを使って、指定したレンジ（英数字）以外の値を抽出しています。

デフォルトのコレーションはケースセンシティブではないと思いますので、コレーションを変更ケースセンシティブのものに変更していない限り、'%[^0-9A-Z]%' や'%[^0-9a-z]%' でも同様の結果を返します。

おまけに、スペースはOKとしたい時は、[^] ワイルドカード内にスペースを追加すれば大丈夫です！

SELECT	*
FROM	Branch
WHERE	BranchCode LIKE '%[^0-9A-Za-z ]%';