1. SQLリファレンス・ガイド
  2. SQL問合せ管理
  3. 比較式
  4. 正規表現の条件

正規表現の条件

regex_like関数では、正規表現の照合が実行されます。正規表現は、正規表現エンジンが入力文字列との照合を試行するパターンです。問合せでregex_like関数を呼び出すための構文は、「関数コール」の項で説明されている他のすべての関数と同じです。regex_like関数には2つのシグネチャがあり、一方のパラメータは2つで、もう一方は3つです。

構文

boolean regex_like(any*, string)
 
boolean regex_like(any*, string, string)

セマンティクス

regex_like関数では、標準SQLのLIKE演算子と同様の機能が提供されます。つまり、この関数を使用して、入力文字列が特定のパターンと一致するかどうかをチェックできます。入力文字列とパターンは、それぞれ1番目と2番目の引数で計算されます。3番目のオプションの引数は、照合の実行方法に影響するフラグ・セットを指定します。

regex_like関数では、通常は、その各引数で単一の文字列が返されると想定されています。そうでない場合は、次のように動作します。

  • 最初の引数が文字列を返さないことがコンパイル時に検出されると、コンパイル時のエラーが発生します。そうではなく、最初の引数で何も返されなかった場合、複数の項目が返された場合、または文字列でもNULLでもない単一の項目が返された場合は、falseが返されます。
  • パターンやフラグが単一の文字列またはNULLを返さない場合は、エラーが発生します。
  • いずれかの引数が単一のNULLを返す場合、NULLを返します。

それ以外の場合、regex_like関数は次のように動作します。

  • パターン文字列が有効でない場合、または長さが512文字を超える場合にエラーが発生します。
  • パターンが入力文字列と一致しない場合は、falseを返します。
  • パターンが入力文字列と一致する場合は、trueを返します。
パターン文字列は、入力テキストと照合する正規表現です。パターン文字列の構文は、Java Patternクラスでサポートされているもののサブセットです。具体的に述べると、正規表現での各文字は、それ自体に一致するリテラル文字(パターン文字列xは文字'x'と一致しているなど)、または特別な意味がある構成を指定するメタ文字です。次のメタ文字のみがサポートされています。
  • ピリオド(.)

    改行を除くすべての文字と照合するには、ピリオドのメタ文字を使用します。

  • グリーディ数量詞(*)

    前の要素が0個以上出現することを示すには、グリーディ数量詞(*)を使用します。

    たとえば、ピリオド(.)と(*)の組合せと文字Dからなる"D.*"は、先頭が文字'D'でその後に0個以上の文字が続く、すべての文字列と一致します。

  • 引用符構成(\、\Q、\E)

    後に続くメタ文字をリテラル文字として解釈するように正規表現エンジンに指示するには、他のメタ文字とともにバックスラッシュ'\'文字を引用符構成として使用します。

    たとえば、リテラル・アスタリスク'*'文字に一致するようにし、それをグリーディ数量詞(*)と解釈しないようにするには、'\*'というパターンを使用します。

    '\Q'文字の後に始まり'\E'文字で終わる正確な文字列パターンと照合するには、問合せで'\Q'および'\E'引用符構成を使用します。

    たとえば、値'**Houston**'を含むすべての文字列と照合するには、"\\Q**Houston**\\E"を使用します。

    ノート:

    SQLシェルや他の同等のツールでは、一重バックスラッシュ'\'引用符構成のかわりに二重バックスラッシュ'\\'を使用する必要があります。
  • エスケープ・シーケンス

    事前定義済の特定の文字と照合するには、エスケープ・シーケンスを使用ます。次のエスケープ・シーケンスがサポートされています。

フラグ文字列には1つ以上の文字が含まれており、それぞれの文字は特定の動作を指定するフラグです。次の表に、使用可能な文字とそのセマンティクスの完全なリストを示します。

表6-2 エスケープ・シーケンス

文字 説明
\x{h...h} 16進値0xh...h (Character.MIN_CODE_POINT <= 0xh...h <= Character.MAX_CODE_POINT)である文字に一致します
\xhh 16進値0xhhである文字に一致します
\uhhhh

16進数0xhhhhで指定されたUnicode文字に一致します。

例: 'u000A'は改行文字に一致します
\t タブ文字('\u0009')に一致します
\r 改行文字('\u000D')に一致します
\n 改行(行送り)文字('\u000A')に一致します
\f 改ページ文字('\u000C')に一致します
\e エスケープ文字('\u001B')に一致します
\cx xに対応する制御文字と一致します
\a アラート(ベル)文字('\u0007')に一致します
\\ バックスラッシュ'\'文字('\u005C')に一致します
\0nn 8進値0nn(0 <= n<= 7)である文字に一致します
\0n 8進値0n (0 <= n <= 7)である文字に一致します
\0mnn 8進値0mnn(0 <= m<= 3, 0 <= n<= 7)である文字に一致します

フラグには1つ以上の文字が含まれており、それぞれの文字フラグにより特定の動作を指定します。次の表では、フラグ文字とそのセマンティクスをすべて一覧表示します。

表6-3 事前定義済の引用符で囲まれた文字

フラグ 名前 説明
"d" UNIX_LINES

Unix行モードを有効にします。

改行文字('\n')は、このモードで認識される唯一の行終了方法です。

"i" CASE_INSENSITIVE

大/小文字を区別しない照合を有効にします。

デフォルトでは、CASE_INSENSITIVE照合では、US-ASCII文字セットの文字のみが一致するとみなされます。このフラグと組み合せてUNICODE_CASEフラグを指定すると、Unicode対応のCASE_INSENSITIVEを有効にできます。

このフラグを指定すると、若干のパフォーマンス・ペナルティが発生する場合があります。
"x" COMMENTS

パターンの空白とコメントを許可します。

このモードでは、空白は無視され、#で始まる埋込みコメントは行の終わりまで無視されます。
"l" LITERAL

LITERALを指定すると、パターンを指定する入力文字列が一連のリテラル文字として扱われます。メタ文字やエスケープ・シーケンスには、特殊な意味はありません。このフラグと組み合せて使用すると、CASE_INSENSITIVEとUNICODE_CASEは照合に及ぼす影響を保持します。その他のフラグは不要になります。
"s" DOTALL

DOTALLモードを有効にします。DOTALLモードでは、式のドット(.)は、行の終了文字を含むすべての文字と一致します。ただし、デフォルトでは、式のドット(.)は行の終了文字と一致しません。
"u" UNICODE_CASE

CASE_INSENSITIVEフラグを有効にした場合、デフォルトでは、US-ASCII文字セット内の文字のみを使用して照合されます。UNICODE_CASEを指定した場合は、Unicode標準を使用したCASE_INSENSITIVE照合が実行されます。

このフラグを指定すると、パフォーマンス・ペナルティが発生する場合があります。
"c" CANON_EQ

このフラグを指定すると、完全な正規分解が一致する場合にのみ、2つの文字が一致するとみなされます。このフラグを指定すると、たとえば、式a\u030Aは文字列\u00E5に一致します。デフォルトでは、照合は標準的な等価を考慮しません。

このフラグを指定すると、パフォーマンス・ペナルティが発生する場合があります。
"U" UNICODE_CHARACTER_CLASS

事前定義済文字クラスおよびPOSIX文字クラスのUnicodeバージョンを有効にします。このフラグを指定すると、(US-ASCIIのみ)事前定義済文字クラスおよびPOSIX文字クラスがUnicode技術標準に準拠します。http://unicode.org/reports/tr18/#Compatibility_Propertiesを参照してください。

フラグはUNICODE_CASEを示し、Unicode対応の大文字/小文字の折りたたみが有効になります。このフラグを指定すると、パフォーマンス・ペナルティが発生する可能性があります。

ノート:

regex_like関数は、索引スキャンの最適化には使用されません。
例のとおりにする場合は、データを作成し、この項で示しているユーザー・データ・アプリケーション表、および例で使用する表のトピックで示している航空会社手荷物追跡アプリケーション表にそれをロードします。

ノート:

SQLシェルから、次の例で示す問合せを実行します。問合せで示しているように、引用符構成には一重バックスラッシュ'\'のかわりに二重バックスラッシュ'\\'を使用してください。

例6-76 姓が'S'で始まるユーザーのリストをユーザー・データ・アプリケーション表からフェッチする

SELECT id, firstName, lastName FROM users WHERE regex_like(lastName, "S.*")

説明: 前述の問合せでは、regex_like関数を使用して、先頭が'S'文字のlastNameフィールドと照合しています。先頭文字が'S'でその後にピリオド・メタ文字(.)とグリーディ数量詞(*)が続くパターンを作成して、任意の文字がゼロ個以上出現するものと照合します。

出力:
{"id":10,"firstName":"John","lastName":"Smith"}
{"id":20,"firstName":"Jane","lastName":"Smith"}
{"id":30,"firstName":"Adam","lastName":"Smith"}
{"id":40,"firstName":"Joanna","lastName":"Smith"}

例6-77 姓に'w'または'W'が1つ以上含まれているユーザーのリストをユーザー・データ・アプリケーション表からフェッチする

SELECT id, lastName FROM users WHERE regex_like(lastname,".*w.*","i")

説明: 前述の問合せでは、目的のパターンと'i'フラグを指定してregex_like関数を使用し、大文字と小文字を区別せずに照合できるようにしています。

出力:
{"id":60,"lastName":"Law"}
{"id":50,"lastName":"Winslet"}

例6-78 ユーザー・データ・アプリケーション表内の電子メール・アドレスの形式を検証する

SELECT id, firstName, lastName, email FROM users WHERE
      regex_like(email,".*@.*\\..*")
説明: 前述の問合せでは、電子メール形式が有効であるユーザーのリストをフェッチしています。ユーザー・データ・アプリケーション表内のemailフィールドには、ユーザーの電子メール・アドレスが含まれています。emailフィールドに'@'文字があり、末尾が'.com'、'.us'、'.in'などの'.string'パターンであると仮定すると、次のようにregex_like関数を使用して電子メール・アドレスを検証できます。
  • @文字の前後にピリオド・メタ文字(.)とグリーディ数量詞(*)の組合せを使用します。この組合せにより、emailフィールド内の'@'文字の前後に他の文字が0個以上出現するものに一致するパターンを作成しています。
  • '.com'などのドメイン名パターンを実現するために、引用符構成バックスラッシュ'\'文字を使用して、ピリオドをピリオド(.)メタ文字ではなくリテラル'.'文字として照合しています。さらに、すべてのドメイン名を考慮に入れるために、ピリオド・メタ文字(.)とグリーディ数量詞(*)の組合せを使用します。

ノート:

この例では、指定した順序で'@'および'.'文字のパターンが使用されているかどうかをチェックすることで、電子メール・アドレス形式のみを検証しています。電子メール・アドレス自体の有効性を保証するものではありません。前述の文字パターンを含むすべての行がフェッチされます。
出力:
{"id":50,"firstName":"Aubrey","lastName":"Winslet","email":"reachaubrey@somemail.co.us"}
{"id":60,"firstName":"Jimmy","lastName":"Law","email":"reachjimlaw@gotmail.co.us"}
{"id":20,"firstName":"Jane","lastName":"Smith","email":"jane.smith201@reachmail.com"}
{"id":10,"firstName":"John","lastName":"Smith","email":"john.smith@reachmail.com"}

例6-79 社会奉仕活動での評価が5つ星であるユーザーのリストをユーザー・データ・アプリケーション表からフェッチする

SELECT id, firstName, lastName FROM users WHERE
      regex_like(communityService,"\\Q*****\\E")
説明: 前述の問合せでは、社会奉仕活動に積極的に関与しているユーザーのリストをフェッチしています。各ユーザーが社会奉仕活動への関与について'*'の数で評価されていると仮定して、ユーザー・データ・アプリケーション表から5つ星評価(つまり、communityServiceフィールドに"*****"がある)のユーザーのリストを取得しています。引用符構成'\Q'および'\E'を指定してregex_like関数を使用し、アスタリスク'*'文字5つのパターンと照合しています。この問合せでは、アスタリスク(*)文字は、グリーディ数量詞(*)ではなくリテラル'*'文字として使用されています。

ノート:

引用符構成なしでパターンを作成した場合は、regex_like関数に指定したパターンが正しくないことを示すエラーが生成されます。
出力:
{"id":20,"firstName":"Jane","lastName":"Smith"}

例6-80 住所フィールドの番地属性にタブ文字の存在が原因のデータ入力エラーの可能性があるユーザーのリストをユーザー・データ・アプリケーション表からフェッチする

SELECT id, firstName, users.address.street FROM users WHERE regex_like(users.address.street, ".*\t.*")
説明: regex_like関数を使用して、エスケープ・シーケンスを含む行を特定できます。サポートされている事前定義済文字の詳しいリストは、表6-2を参照してください。この問合せでは、addressフィールドのstreet属性に誤ってタブ文字が含まれているユーザーのリストをユーザー・データ・アプリケーション表からフェッチしています。次のパターンを指定してregex_like関数を使用します。
  • タブ文字を特定するためにエスケープ・シーケンス'\t'を使用します。
  • エスケープ・シーケンスの前後にピリオド・メタ文字(.)とグリーディ数量詞(*)の組合せを使用して、street属性に他の文字が0個以上出現しているものを考慮に入れます。
出力:
{"id":70,"firstName":"Dierdre","street":"Maine\t(Suburb)"}
または、次の問合せで示すように、対応するUnicodeの16進文字を使用してエスケープ・シーケンスを照合できます。Unicode値0x0009は、水平タブまたはタブ文字に対応しています。前述と同じ出力が表示されます。
SELECT id, firstName, users.address.street FROM users WHERE regex_like(users.address.street,
      ".*\u0009.*")

ノート:

Oracle NoSQL Databaseでは、SQLシェルまたは同等のツールでUnicodeの16進値を使用して、制御文字(ASCIIコード0~31)、およびASCIIコードが128より大きい文字を挿入できます。

たとえば、ユーザー・データ・アプリケーション表で示されているように、Unicodeの16進値0x001Bを使用してエスケープ(ESC)文字を挿入できます(idが70の行にあるstateフィールドを参照)。

対応する行をフェッチする場合は、次の問合せで示されているように'\e'を使用して正規表現でパターンを照合します。
SELECT id, firstName, users.address[].state FROM users WHERE
      EXISTS(users.address[regex_like($element.state, ".*\\e")])

説明: この問合せでは、addressフィールドのstate属性に末尾がエスケープ文字の文字列が含まれているかどうかを確認しています。引用符構成'\'を使用して、ピリオド・メタ文字(.)とグリーディ数量詞(*)の組合せが前にあるエスケープ文字を照合し、エスケープ文字の前に他の文字が0個以上出現するものを考慮に入れています。

出力:
{"id":70,"firstName":"Dierdre","state":"TX\u001B"}

例6-81 旅行の出発地と最終到着地の間の中間経由地としてORD空港を経由したすべての手荷物を検索する

SELECT 
ticketNo AS TICKETNUM,
fullName AS NAME, 
BaggageInfo.bagInfo.routing[] AS ROUTING
FROM BaggageInfo WHERE regex_like(BaggageInfo.bagInfo.routing[],".*/ORD/.*")
説明: 航空会社手荷物追跡アプリケーションでは、特定の空港を経由する必要があった乗客のリストをフェッチできます。乗客ごとに、BaggageInfo表内のroutingフィールドに、出発地/経由地/到着地という形式で空港コードが含まれています。この問合せでは、次のようにregex_like関数を使用して経由空港の空港コードを照合しています。
  • すべての出発地空港コード文字を考慮に入れるために、ピリオド(.)メタ文字とグリーディ数量詞(*)の組合せを使用します。
  • 2つのスラッシュ'/'文字の間に、空港コードとして経由空港ORDを含めます。
  • すべての到着地空港コード文字を考慮に入れるために、ピリオド(.)メタ文字とグリーディ数量詞(*)の組合せを使用します。
出力:
{"TICKETNUM":176234463813,"NAME":"Henry Jenkins","ROUTING":"SFO/ORD/FRA"}
{"TICKETNUM":1762392135540,"NAME":"Adelaide Willard","ROUTING":"GRU/ORD/SEA"}

例6-82 連絡先電話番号の市外局番が364である乗客をすべて検索します。

SELECT ticketNo, contactPhone, fullName FROM BaggageInfo WHERE regex_like(contactPhone,"364-.*")
説明: 航空会社手荷物追跡アプリケーションでは、連絡先電話番号の市外局番が同一である乗客のリストをフェッチできます。contactPhoneフィールドには、米国の連絡先番号が、3桁の市外局番の後に7桁の市内番号という形式で含まれています。contactPhoneフィールドにある連絡先電話番号パターンがXXX-YYY-ZZZZであり、X、YおよびZが0から9までの数字であると仮定して、次のようにregex_like関数を使用しています。
  • 市外局番364の後にハイフン'-'文字を使用します。
  • 3桁の数字の後にハイフン'-'文字と4桁の数字が続くパターン照合を可能にするために、ピリオド(.)メタ文字とグリーディ数量詞(*)の組合せを使用します。
出力:
{"ticketNo":1762320369957,"contactPhone":"364-610-4444","fullName":"Lorenzo Phil"}
{"ticketNo":1762320569757,"contactPhone":"364-610-4455","fullName":"Lucinda Beckman"}

ユーザー・データ・アプリケーション表

次のコードでは、ユーザー・データ・アプリケーション表usersが作成されます。
CREATE TABLE users ( id INTEGER,
                     firstName STRING,
                     lastName STRING,
                     otherNames ARRAY(RECORD(first STRING, last STRING)),
                     age INTEGER,
                     income INTEGER,
                     address JSON,
                     connections ARRAY(INTEGER),
                     email STRING,
                     communityService STRING,
PRIMARY KEY (id) )
次のコードでは、users表にサンプル行が移入されます。
INSERT INTO users VALUES ( 

   10,   
   "John",
   "Smith",
   [ {"first" : "Johny", "last" : "Good"}, {"first" : "Johny2", "last" : "Brave"},{"first" : "Johny3", "last" : "Kind"},{"first" : "Johny4", "last" : "Humble"} ],
   22,
   45000,
   {
      "street" : "Pacific Ave",
      "number" : 101,
      "city" : "Santa Cruz",
      "state" : "CA",
      "zip" : 95008,
      "phones" : [
         { "area" : 408, "number" : 4538955, "kind" : "work" },
         { "area" : 831, "number" : 7533341, "kind" : "home" },
         { "area" : 831, "number" : 7533382, "kind" : "mobile" }
      ]
   },
   [ 30, 55, 43 ],
   "john.smith@reachmail.com",
   "****"
)

INSERT INTO users VALUES ( 

   20,   
   "Jane",
   "Smith",
   [ {"first" : "Jane", "last" : "BeGood"} ],
   22,
   55000,
   {
      "street" : "Atlantic Ave",
      "number" : 201,
      "city" : "San Jose",
      "state" : "CA",
      "zip" : 95005,
      "phones" : [
         { "area" : 608, "number" : 6538955, "kind" : "work" },
         { "area" : 931, "number" : 9533341, "kind" : "home" },
         { "area" : 931, "number" : 9533382, "kind" : "mobile" }
      ]
   },
   [ 40, 75, 63 ],
   "jane.smith201@reachmail.com",
   "*****"
)

INSERT INTO users VALUES ( 

   30,   
   "Adam",
   "Smith",
   [ {"first" : "Adam", "last" : "BeGood"} ],
   45,
   75000,
   {
      "street" : "Indian Ave",
      "number" : 301,
      "city" : "Houston",
      "state" : "TX",
      "zip" : 95075,
      "phones" : [
         { "area" : 618, "number" : 6618955, "kind" : "work" },
         { "area" : 951, "number" : 9613341, "kind" : "home" },
         { "area" : 981, "number" : 9613382, "kind" : "mobile" }
      ]
   },
   [ 60, 45, 73 ],
   "adam.smith201reachmail.com",
   "***"
)

INSERT INTO users VALUES ( 

   40,   
   "Joanna",
   "Smith",
   [ {"first" : "Joanna", "last" : "Smart"} ],
   NULL,
   75000,
   {
      "street" : "Tex Ave",
      "number" : 401,
      "city" : "Houston",
      "state" : "TX",
      "zip" : 95085,
      "phones" : [
         { "area" : NULL, "number" : 1618955, "kind" : "work" },
         { "area" : 451, "number" : 4613341, "kind" : "home" },
         { "area" : 481, "number" : 4613382, "kind" : "mobile" }
      ]
   },
   [ 70, 30, 40 ],
   NULL,
   "**"
)

INSERT INTO users VALUES ( 

   50,   
   "Aubrey",
   "Winslet",
   [ {"first" : "Aubrey", "last" : "Regal"} ],
   NULL,
   15000,
   {
      "street" : "Tex Ave",
      "number" :501,
      "city" : "Houston",
      "state" : "TX",
      "zip" : 95085,
      "phones" : [
         { "area" : 723, "number" : 7618955, "kind" : "work" },
         { "area" : 751, "number" : 7613341, "kind" : "home" },
         { "area" : 781, "number" : 7613382, "kind" : "mobile" }
      ]
   },
   [ 50, 20, 40 ],
   "reachaubrey@somemail.co.us",
   "****"
)

INSERT INTO users VALUES ( 

   60,   
   "Jimmy",
   "Law",
   [ {"first" : "Jimmy", "last" : "Smart"} ],
   NULL,
   25000,
   {
      "street" : "Maine",
      "number" :501,
      "city" : "San Jose",
      "state" : "TX",
      "zip" : 95085,
      "phones" : [
         { "area" : 223, "number" : 2618955, "kind" : "work" },
         { "area" : 251, "number" : 2613341, "kind" : "home" },
         { "area" : 281, "number" : 2613382, "kind" : "mobile" }
      ]
   },
   [ 50, 20, 40 ],
   "reachjimlaw@gotmail.co.us",
   "***"
)

INSERT INTO users VALUES ( 

   70,   
   "Dierdre",
   "Amador",
   [ {"first" : "Dierdre", "last" : "Kind"} ],
   NULL,
   25000,
   {
      "street" : "Maine\t(Suburb)",
      "number" :701,
      "city" : "San Jose",
      "state" : "TX\u001B",
      "zip" : 95085,
      "phones" : [
         { "area" : 223, "number" : 6718955, "kind" : "work" },
         { "area" : 251, "number" : 6213341, "kind" : "home" },
         { "area" : 281, "number" : 6213382, "kind" : "mobile" }
      ]
   },
   [ 10, 60, 40 ],
   NULL,
   "***"
)