SQL Server 入門 SQL Server 入門

ホーム > 便利なT-SQL&クエリー集 > 英数字以外が含まれている値を抽出する

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

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

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

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


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

例えば次のような 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'ニューヨーク第二支店');

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


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

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

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

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

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

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


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

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

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

SQL Server 関連の人気書籍
ホーム > 便利なT-SQL&クエリー集 > 英数字以外が含まれている値を抽出する