T-SQL - PATINDEX の使い方 - 文字列のパターン検索に便利！

知っておくと便利な関数 - PATINDEX の使い方

ここでは、文字列のパターン検索に便利なビルトイン関数のひとつ、PATINDEX 関数についてご説明します。


PATINDEX 関数

PATINDEX 関数は、指定したパターンが検索対象の文字列の中の何文字目から始まるかを返してくれる関数で、構文は次の通りです。 

PATINDEX ('%パターン%', 検索対象の文字列)

PATINDEX に指定するパターンは、ワイルドカード（%, _, [], [^]）など含むパターンである必要があります。

  • %: 0 文字以上の任意の文字列
  • _: 任意の 1 文字
  • []: 指定された任意の 1 文字
  • [^]: 指定以外の任意の 1 文字

戻り値は、パターンが最初に現れる位置で、位置のカウントは一番最初の文字を 1 としてカウントします。

パターンが、対象文字列の中に複数回現れる場合は、最初の出現の位置を返します。

指定した文字が出現しない場合は 0 を返します。


それでは、PATINDEX 関数を使ってパターンが現れる位置を確認してみましょう。

DECLARE @Value VARCHAR(50) = '12345abcd678bcd9';

SELECT PATINDEX('%cd%', @Value),
       PATINDEX('%_45%', @Value),
       PATINDEX('%[a-z]%', @Value),
       PATINDEX('%[^0-9]%', @Value),
       PATINDEX('%z%', @Value);

T-SQL - PATINDEX の使い方 1


@Value は '12345abcd678bcd9' という VARCHAR 型の文字列です。

PATINDEX('%cd%', @Value) は、cd という文字列が最初に現れる位置を返すので、8 が返っています。

PATINDEX('%_45%', @Value) は、任意の1文字に 45 が続いている位置を返すので、それにマッチする 345 の 3 の位置である、3 が返っています。

PATINDEX('%[a-z]%', @Value) では、アルファベットの a から z のどれかが最初に現れる位置を返すので、a の位置である 6 が返っています。

PATINDEX('%[^0-9]%', @Value) では、0 から 9 以外の文字が最初に現れる位置を返すので、a の位置である 6 が返っています。

PATINDEX('%z%', @Value) は、z が現れる位置を返しますが、z は @Value の中に存在していないので、0 を返しています。


PATINDEX 関数を使って、英数字以外の文字が含まれているかチェックすることもできます。

例えば、Student テーブルの StudentNumber に、英数字以外の文字が含まれているレコードとその出現位置を取得するには次のようにできます。

SELECT StudentNumber,
       PATINDEX('%[^a-z0-9]%', StudentNumber)
FROM   Student
WHERE  PATINDEX('%[^a-z0-9]%', StudentNumber) > 0;

T-SQL - PATINDEX の使い方 2

StudentNumber に $ や @ などの、英数字以外が入ったレコードと、その出現位置が取得できていますね。

日本やアメリカの SQL Server のデフォルトの照合順序 (Collation) は Case Insensitive だと思いますが、もし Case Sensitive な Collation を使っている場合は、PATINDEX('%[^a-zA-Z0-9]%', StudentNumber) にしてください。

また、出現位置は必要なく、英数字以外の文字が含まれているレコードをフィルターするだけでよければ、WHERE StudentNumber LIKE '%[^a-z0-9]%' でも同じ結果が得られます。


以上、文字列のパターン検索に便利なビルトイン関数のひとつ、PATINDEX 関数についてご説明しました。

© 2010-2025 SQL Server 入門