VBAでSQLITEにパターンマッチしたいのに罠が掘ってある！

コンテンツ

vbRichClientさんを利用してSQLiteをVBAからいじっていますが、時折訪れる絶望感に毎度心が折れかけ中です。
SELECT文でレコード情報(cRecordSet型オブジェクト)を取得しますが、プリペアドステートメントの使う使わないどちらにせよ、下記のコマンドににかなり制限があるようです。

SELECT * FROM テーブル名 WHERE カラム名 LIKE パターン [ESCAPE エスケープ判定文字];

どんな罠なのか

①ESCAPE文でsyntax errorが発生する

他のツールから利用しても、それこそコマンドプロンプトからですら正常に動作するはずのSELECT文も、vbRichClient様にかかればsyntax errorです。

②パターンマッチ用演算子の’_’なんてなかった

任意の1文字を意味するはずの’_’が演算子として認識されず、ただの’_’という文字として扱われます。

③パターンマッチ用演算子の’%’は文字の先頭と末尾だけ

任意の0文字以上の文字列を意味する’%’はきちんと動作します。
パターン文字列の先頭と末尾だけですけど！
パターン文字列の文中にある場合は、ただの’%’という文字として扱われます。

④1文字 + % 構成でも認識しないから

例えばレコードには「山田太郎」が入っているとすると「山田%」や「%山%」がHitするのに、「山%」だとHitしないというもの。

⑤データベースに入っている文字列にアルファベットや数字入っているとちゃんと検索しないから

これが一番まずくて、検索先のカラムに「山田太郎RX」とか入っていると
「山田%」でHit
「山田太%」でHitせず(!?)
「山田太郎R%」でHitせず(!?)
「山田太郎RX」でHitする
というわけわからん動きをします。

対策

①については、無視します。
自分の利用環境上、「検索対象カラムには全角文字しか入っていない」という素晴らしい大前提があるため、’~’でエスケープする演算子が半角な以上、必ず検索Hit数は0です。
ですので、パターン文字列に’~’が検出された時点で検索失敗とみなすことで回避しました。

②と③は同時に見ていきます。
‘%’だろうと’‘だろうとパターン文中にあればアウトです。さらに’‘はパターン先頭末尾であろうと認識しません。ので、

‘_’は’%’に置換する
置換結果から、パターン先頭末尾以外に’%’があれば以降を切り捨てる
上記の条件でレコードを検索
検索結果を元のパターンにマッチするか確認して、マッチしないものは除外する

という順序で対応します。

で、④ですが最終的に1文字 + %になっていたら先頭に%をつけて強制的に構成を直し、パターンマッチ対象にしますよ。

⑤は検索条件というより、検索先のデータ依存なのでどうしようかと思いましたが、全部頭に%をつけることで回避は可能っぽい。

今回、名前が格納されているカラムからパターンにマッチするレコードを取る、としましょう。
パターン文字列に含まれる’%%’などは’%’に変換する等の最適化は既にしてあるとします。

Dim strName As String
Dim bIsNameUsedWC As Boolean

strName = 本来利用したかったパターン文字列

' 検索対象には全角文字しか含まれないため、運用上チルダが見つかったら検索に失敗
If Strings.InStr(strName, "~") > 0 Then
    GoTo ExitFunction
End If

' ワイルドカード?のパターンマッチ演算子_については%に置き換えて検索し、後で間引きする
bIsNameUsedWC = (Strings.InStr(strName, "_") > 0)
strName = Strings.Replace(strName, "_", "%")

' 前後の%以外の%も認識しないため、最終的に文字中に%が検出された場合も後で間引きする
nPos = InStr(strName, "%")
If nPos = 1 Then
    nPos = InStr(nPos + 1, strName, "%")
End If

If (nPos <> 0) And (nPos <> 1) And (nPos <> Len(strName)) Then
    ' 文中に検出されたら、検出前まで切り落として前方一致
    strName = Strings.Left$(strName, nPos - 1) & "%"
    bIsNameUsedWC = True
End If

' ここまでくると最終的に%のみが残るが、dbRichClientの不具合で
' "○%"と1文字 + %構成になってしまうと検索がHitしなくなる
' 1文字 + %構成であれば頭に%をつける
If (Len(strName ) = 2) And (Strings.InStr(1, strName , "%") = 2) Then
    strName = "%" & strName 
    bIsNameUsedWC = True
End If

' dbRichClientの不具合で、DB格納データにアルファベットや数字が混在している場合
' 頭に%をつけないと検出できないため、%始まりでない場合は%をつける
If 1 <= Len(strName) Then
    If Strings.Left$(strName, 1) <> "%" Then
        strName = "%" & strName
        m_bIsNameUsedWC = True
    End If
End If

ExitFunctionラベルについては説明しませんが、検索Hit数 = 0になるように手頃な位置に宣言しておきましょう。
これでstrNameをLIKE後にぶち込んでcRecordSetオブジェクトを取得します。
ここでは検索対象カラムがNAMEとします。

Dim strPattern As String
Dim strHitName As String

Set objRecordSet = オブジェクトが取れたとします

' 問い合わせ結果の判定
Do While Not objRecordSet.EOF
    ' 除外対象でないか判定
    If bIsNameUsedWC Then
        strHitName = CStr(objRecordSet.Field(0).Value)

        ' Excelのパターンマッチ演算子に戻して比較
        strPattern = Strings.Replace(Strings.Replace( _
            本来利用したかったパターン文字列, "_", "?"), "%", "*")
        If Not (objSH.strName Like strPattern) Then
            GoTo NextSerachResult
        End If
    End If
    
    MsgBox strHitName & "が見つかったよ！"

NextSerachResult:
    objRecordSet.MoveNext
Loop

これでなんとかする予定！

他にもニッチなIT関連要素をまとめていますので、よければ一覧記事もご覧ください。