TOP  Index  Search  Changes  RSS  Login

拡張SQL

拡張 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) = match('pattern', expr, 'ckwv')に展開されます。match('pattern', expr, '') は使用する意味がないので、高速な like 演算子を使用してください。

 なお、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('%かいがら%')
Last modified:2013/01/28 22:24:20
Keyword(s):
References:[SideMenu] [SQLタブ] [テクニカルノート]