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