差分
このページの2つのバージョン間の差分を表示します。
次のリビジョン | 前のリビジョン | ||
technote:sqlex [2022/09/25 16:41] – 作成 kabe | technote:sqlex [2022/10/12 22:58] (現在) – [regexp 演算子 (ビルトイン)] ohyeah | ||
---|---|---|---|
行 1: | 行 1: | ||
====== 拡張SQL ====== | ====== 拡張SQL ====== | ||
+ | |||
+ | ACFinder では、[[https:// | ||
+ | |||
+ | 機能拡張の方法として、SQLite データベースエンジンに関数・演算子を組み込むビルトイン関数と、SQLite データベースエンジンに SQL を渡す前に ACFinder 側で処理するプリプロセッサ関数があります。 | ||
+ | |||
+ | ===== match 演算子 (ビルトイン) ===== | ||
+ | 「大文字/小文字」「ひらがな/カタカナ」「全角/半角」の違い及び「表記の揺れ」を全て同一視する、like 類似演算子です((SQLite3 では match 演算子の実装はユーザに任せられているので、SQLite3 を使った他のシステムの match 演算子が同じ機能を持つとは限りません))。1文字ワイルドカードの ' | ||
+ | < | ||
+ | 完全一致: | ||
+ | 前方一致: | ||
+ | 後方一致: | ||
+ | 部分一致: | ||
+ | </ | ||
+ | 同一視したくない文字種がある場合は、match 関数を使用してください。option パラメータは省略可能で、省略した場合は ' | ||
+ | なお、match 演算子は、内部で match(' | ||
+ | < | ||
+ | MATCH(' | ||
+ | |||
+ | option | ||
+ | c: 大文字/小文字の違いを同一視する | ||
+ | k: ひらがな/カタカナの違いを同一視する | ||
+ | w: 全角/半角の違いを同一視する | ||
+ | v: 濁音、半濁音、長音、拗音、促音などの表記の揺れを同一視する | ||
+ | </ | ||
+ | ACFinder 080921 版以降では、' | ||
+ | |||
+ | |||
+ | ===== regexp 演算子 (ビルトイン) ===== | ||
+ | |||
+ | [[http:// | ||
+ | < | ||
+ | expr REGEXP ' | ||
+ | </ | ||
+ | Perl 5.8 互換といっても、修飾子(modifier)は使えません。その代わり独自拡張として、「大文字/小文字」「ひらがな/カタカナ」「全角/半角」の違い及び「表記の揺れ」を全て同一視するようになっています。 | ||
+ | |||
+ | 同一視したくない文字種がある場合は、regexp 関数を使用してください。書式及び option の意味は match 関数と同じです。全ての文字種の違いを識別する場合は、 | ||
+ | |||
+ | < | ||
+ | REGEXP(' | ||
+ | </ | ||
+ | となります。regexp 演算子は、内部で regexp(' | ||
+ | |||
+ | regexp を件数が多いデータベースで使用すると、検索が遅くなります。単純に文字種の違いを同一視するだけなら、高速な match 演算子をお使いください。 | ||
+ | |||
+ | なお、regexp は実行時にパターンを「大文字→小文字」「ひらがな→カタカナ」「全角→半角」にノーマライズします。このため、次のような制約があります。 | ||
+ | |||
+ | * regexp 演算子または ' | ||
+ | 大文字を小文字化するため、\S, | ||
+ | |||
+ | * regexp 演算子または ' | ||
+ | 全角文字を半角化するため、メタキャラクタに該当する「()[]{}.?*+-^$¥」等は、全角文字であっても \ でエスケープする必要があります。 | ||
+ | |||
+ | ===== concat 関数 (ビルトイン) ===== | ||
+ | |||
+ | 集約関数、スカラー関数いずれにも使用可能な文字列連結関数です。他の DBMS の concat 関数と異なり、下記の点が異なります。 | ||
+ | * 接続する文字列と文字列の間に挿入する接続文字列を指定できる | ||
+ | * NULL や空文字列はもちろん、連結文字列中にすでに存在する文字列は連結しない | ||
+ | |||
+ | ==== concat 集約関数 ==== | ||
+ | 検索された各行(レコード)から特定フィールド等に存在する文字列を連結します。下記のような式で、第1パラメータの connector は接続文字列、第2パラメータ expr は連結する文字列を表す式(通常はフィールド名)を記述します。 | ||
+ | < | ||
+ | CONCAT(' | ||
+ | </ | ||
+ | |||
+ | ==== concat 関数 ==== | ||
+ | 下記のような式で、第2パラメータ(expr1)以降の式を左から順に、第1パラメータ(connector)の接続文字列を挟みながら連結します。パラメータ数は可変ですが、第1パラメータを含めて最小で 3、最大で 127 です。 | ||
+ | < | ||
+ | CONCAT(' | ||
+ | </ | ||
+ | 要するに、下記 (1) の式を (2) の式のように書けるということになります。 | ||
+ | < | ||
+ | (1) ' | ||
+ | (2) CONCAT(', | ||
+ | </ | ||
+ | |||
+ | ===== concat2 関数 (ビルトイン) ===== | ||
+ | 集約関数、スカラー関数いずれにも使用可能な文字列連結関数です。上の concat 関数との違いは、連結文字列中にすでに存在する文字列も連結するところです。 | ||
+ | < | ||
+ | CONCAT(', | ||
+ | CONCAT2(', | ||
+ | </ | ||
+ | の結果は、それぞれ下記のようになります。 | ||
+ | < | ||
+ | ABC | ||
+ | ABC, ABC | ||
+ | </ | ||
+ | |||
+ | ===== extnum 関数 (ビルトイン) ===== | ||
+ | 下記のような式により、expr に「数字+unit-pattern」が存在する場合に、数字部分(整数だけでなく固定小数点・浮動小数点にも対応)を抜き出します。unit-pattern は ' | ||
+ | |||
+ | 「数字+unit-pattern」が存在しない場合は、default で指定した数値を返します。default は省略可能で、省略した場合は 0 を返します。 | ||
+ | < | ||
+ | EXTNUM(' | ||
+ | </ | ||
+ | |||
+ | ===== re_replace 関数 (ビルトイン) ===== | ||
+ | [[http:// | ||
+ | < | ||
+ | RE_REPLACE(' | ||
+ | </ | ||
+ | |||
+ | ===== ifnullstr 関数 (ビルトイン) ===== | ||
+ | 標準ビルトイン関数の IFNULL と使い方は同じですが、IFNULL(X, | ||
+ | < | ||
+ | CASE WHEN X ISNULL OR X = '' | ||
+ | </ | ||
+ | |||
+ | ===== if 関数 (ビルトイン) ===== | ||
+ | 使い方は Excel の IF 関数と同様に、第1パラメータに条件式、第2パラメータに第1パラメータが真の場合の返り値、第3パラメータに第1パラメータが偽の場合の返り値を指定します。 | ||
+ | < | ||
+ | IF(expr-condition, | ||
+ | </ | ||
+ | これは、下記の CASE 文で代替できます。 | ||
+ | < | ||
+ | CASE WHEN expr-condition THEN expr-true ELSE expr-false END | ||
+ | </ | ||
+ | |||
+ | ===== strconv 関数(ビルトイン) ===== | ||
+ | 第一パラメータの expr で渡された文字列を「大文字/小文字」「ひらがな/カタカナ」「全角/半角」の違い及び「表記の揺れ」を正規化します。expr は式ですが、一般的にはフィールドを指定します。 | ||
+ | |||
+ | 正規化する文字種は、第二パラメータの option で指定します。option は、MATCH 関数と同じです。option を省略した場合は、' | ||
+ | < | ||
+ | STRCONV(expr[, | ||
+ | </ | ||
+ | ===== preconv 関数 (プリプロセッサ) ===== | ||
+ | strconv ビルトイン関数のプリプロセッサ版です。文字列定数を正規化する際に使用します。 | ||
+ | |||
+ | 下記の3つは意味的には等価ですが、速度は (3) > (1) > (2) の順に速いので、検索する文字列が定数の場合は (3) の使用をお勧めします。 | ||
+ | < | ||
+ | (1) byochu match ' | ||
+ | (2) strconv(byochu) like strconv(' | ||
+ | (3) strconv(byochu) like preconv(' | ||
+ | </ | ||
+ |