【Access】クエリで大文字/小文字、全角/半角、ひらがな/カタカナを区別して文字列比較したい

2019年10月14日Query

String comparison by distinguishing uppercase / lowercase and full-width / half-width in query

Accessのクエリ(SQL)の抽出条件や、演算フィールドの式で文字列比較をするとき、テキストモードでの比較になるので、 大文字/小文字、全角/半角、ひらがな/カタカナを区別しません。

例えば、 「ACCESS」と「Access」と「ＡＣＣＥＳＳ」を等しいとみなします。また、「アクセス」と「あくせす」も等しいとみなします。

しかし、これらを別と判断してほしい場合もあるでしょう。その場合の対処法を紹介します。

完全一致の比較

完全に一致するかどうかを比較するときは、StrComp関数が使えます。

StrComp 関数 (Visual Basic for Applications) | Microsoft Docs

構文　StrComp(string1、 string2、[ compare ])

第3引数(compare)で、文字列比較のモードを指定できます。 0 (vb定数では vbBinaryCompare) を指定すればバイナリモードの比較になり、小文字、全角/半角、ひらがな/カタカナを区別して比較します。完全一致した場合のみ 0 を返します。

式の例　 InStr(1, [フィールド1], "Access", 0)

クリエの抽出条件にするには、下図のように演算フィールドを作成して抽出条件に0を設定します。

QryBinaryCompare

SQLでは、下記のようになります。

SELECT テーブル1.*
FROM テーブル1
WHERE StrComp([フィールド1], "Access", 0)=0;

これでAccessのみ抽出されて、"ACCESS"や"Ａｃｃｅｓｓ"は抽出されません。

部分一致での比較

Like演算子で、抽出条件欄に
Like "*Access*"
というように指定すると、"ACCESS"や"Ａｃｃｅｓｓ"を含むデータも抽出されます。

ここで"Access"のみ抽出したい場合、InStr関数を使います。

InStr関数（Visual Basic for Applications） | Microsoft Docs

構文　InStr([ start ], string1, string2, [ compare ])

第4引数で文字列比較モードを設定します。
やはり、0 を指定するとバイナリモードの比較になります。フィールド1 に"Access"が含まれるときは 1以上の数が返ります。含まれないときは 0 が返ります。

式の例　 InStr(1, [フィールド1], "Access", 0)

クエリの抽出条件での設定は下図のようになります。抽出条件に>0を設定します。

SQLでは、 下記のようになります。

SELECT テーブル1.*
FROM テーブル1
WHERE InStr(1, [フィールド1], "Access", 0)>0;

前方一致での比較

Like演算子なら、抽出条件欄に
Like "Access*"
と設定する条件です。先頭部分が一致するデータを抽出します。

これをバイナリモードでの比較にするには、上記の部分一致の場合の抽出条件欄の式を>0から=1に変更するだけです。

SQLでは、 下記のようになります。

SELECT テーブル1.*
FROM テーブル1
WHERE InStr(1, [フィールド1], "Access", 0)=1;

後方一致での比較

Like演算子なら、抽出条件欄に
Like "*Access"
と設定する条件です。後方部分が一致するデータを抽出します。

これをバイナリモードの比較にするには、クエリで下記のように設定します。

フィールド式1: StrComp(Right([フィールド1],Len("Access")),"Access",0)
抽出条件0

SQLでは、 下記のようになります。

SELECT テーブル1.*
FROM テーブル1
WHERE StrComp(Right([フィールド1],Len("Access")),"Access",0)=0;

Like演算子代替ユーザー定義関数を作成する

上記のように関数を組み合わせれば、バイナリモードでの比較は可能ですが、結構面倒です。

また、 部分一致、前方一致、後方一致 だけでなく Like演算子で可能なもう少し複雑な文字列比較をバイナリモードで行いたい、という場合もあるでしょう。

そうなるとユーザー定義関数を作成することになります。VBAの場合は、モジュール単位で文字列の比較モードを宣言することができます。 これを使うと簡単な関数でできます。

ユーザー定義関数の作成

まず、標準モジュールを新規作成します。先頭に既に2行宣言文がありますがそれを削除して、下記のコードをコピーして貼り付けます。

Option Compare Binary 'バイナリモードで文字列比較
Option Explicit

Public Function LikeB(s1, s2) As Boolean

    If IsNull(s1 + s2) Then Exit Function
    LikeB = s1 Like s2

End Function

メニューの[デバッグ]-[〇〇〇のデバッグ]をクリックします。

[ファイル]-[〇〇〇の上書き保存]をクリックするとモジュール名を聞かれますので、mdlBinaryCompare としてOKをクリックします。

※ 〇〇〇 の部分はプロジェクト名です。

これでこの関数がクエリやテキストボックスのコントロールソース、また、VBAコード内で使用できるようになります。

使用例

クエリで部分一致の条件にしたい場合は、クエリで下記のように設定します。

フィールド式1: LikeB([フィールド1], "*Access*")
抽出条件True

ＳＱＬなら、下記のようになります。

SELECT テーブル1.*
FROM テーブル1
WHERE LikeB([フィールド1], "*Access*");

通常のLike演算子なら、[フィールド1] Like "*Access*"と記述するのを LikeB([フィールド1], "*Access*")というように変更するだけです。

ワイルドカード文字は * だけでなく?(任意の 1 文字)や #(1～9の数字) も使えますし、[]で囲んだ文字のリストも使用できます。Like演算子とまったく同じように使えます。違いはバイナリモードに比較になるということだけです。

例えば、

SELECT テーブル1.* FROM テーブル1 WHERE LikeB([フィールド1], "A[A-Z]####");

とすれば、AX1234 は抽出されますが、ax1234 や AX123a は抽出されません。

Like演算子の詳細は下記をご参照ください。

Like 演算子 (Microsoft Access SQL) | Microsoft Docs

補足

このモジュールの先頭で Option Compare Binary と宣言してますので、このモジュール内の関数での演算子によるテキスト比較はすべてバイナリモードになります。

バイナリモードで比較したい処理がある場合は、このモジュールに放り込んでおくといいでしょう。

まとめ

クエリでの文字列比較はテキストモードになり、それを変更するオプションもないので、大文字/小文字、全角/半角、ひらがな/カタカナを区別する比較は通常はできません。

それを標準の関数を利用して完全一致、部分一致、前方一致、後方一致について、バイナリモードでの比較を実現する方法を紹介しました。

さらにLike演算子と同様の文字列比較がバイナリーモードで可能なユーザー定義関数を紹介しました。ユーザー定義関数は初心者の方でもコピペするだけで簡単に使用できますので、バイナリーモードでの比較が必要なときはぜひご利用ください。

QueryAccess,Query,VBA

Posted by hatena