Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (日本語) » SQL Anywhere サーバ - SQL の使用法 » データのクエリと変更 » データのクエリ » 全文検索のタイプ

 

チュートリアル:GENERIC テキスト・インデックスへの全文検索の実行

次の手順に従って、GENERIC 単語区切りを使用するテキスト・インデックスに全文検索を実行します。

参照:チュートリアル:あいまい全文検索の実行

♦  GENERIC テキスト・インデックスへの全文検索の実行
  1. テキスト設定オブジェクトを作成します。

    次の例では、myTxtConfig というテキスト設定オブジェクトを作成します。FROM 句を含めて、テンプレートとして使用するテキスト設定オブジェクトを指定する必要があります。

    CREATE TEXT CONFIGURATION myTxtConfig FROM default_char;
  2. テキスト設定オブジェクトをカスタマイズします。

    because、about、therefore、only という単語を含むストップリストを追加します。次に、単語の最大長を 30 に設定します。次のように、個別の ALTER TEXT CONFIGURATION 文で設定する必要があります。

    ALTER TEXT CONFIGURATION myTxtConfig
       STOPLIST 'because about therefore only';
    ALTER TEXT CONFIGURATION myTxtConfig
       MAXIMUM TERM LENGTH 30;
  3. MarketingInformation テーブルのコピーを作成します。

    1. Sybase Central で [テーブル] フォルダを拡張します。

    2. [MarketingInformation] を右クリックし、[コピー] を選択します。

    3. [テーブル] フォルダを右クリックし、[貼り付け] を選択します。

    4. [名前] フィールドに、MarketingInformation1 と入力します。[OK] をクリックします。

  4. Interactive SQL で次のコマンドを実行し、新しいテーブルにデータを設定します。

    INSERT INTO MarketingInformation1
       SELECT * FROM MarketingInformation;
  5. デモ・データベースの MarketingInformation1 テーブルの Description カラムに、myTxtConfig テキスト設定オブジェクトを参照するテキスト・インデックスを作成します。再表示間隔を 24 時間に設定します。

    CREATE TEXT INDEX myTxtIndex ON MarketingInformation1 ( Description ) 
       CONFIGURATION myTxtConfig
       AUTO REFRESH EVERY 24 HOURS;
  6. 次の文を実行して、テキスト・インデックスを再表示します。

    REFRESH TEXT INDEX myTxtIndex ON MarketingInformation1;
  7. 次の文を実行して、テキスト・インデックスをテストします。

    1. この文により、テキスト・インデックスで cotton または cap という単語が検索されます。結果はスコアの降順でソートされます。テキスト・インデックスで、cap という単語が出現する頻度は cotton よりも低いため、Cap のスコアの方が高くなります。

      SELECT ID, Description, ct.*
         FROM MarketingInformation1
           CONTAINS ( Description, 'cotton | cap' ) ct
         ORDER BY score DESC;
      ID Description score
      905 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball 「Cap」</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A lightweight wool 「cap」 with mesh side vents for breathable comfort during aerobic activities. Moisture-absorbing headband liner.</span></p></body></html> 2.2742084275032632
      904 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball 「Cap」</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This fashionable hat is ideal for glacier travel, sea-kayaking, and hiking. With concealed draw cord for windy days.</span></p></body></html> 1.6980426550094467
      908 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown 「cotton」 hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage. </span></p></body></html> 0.9461597363521859
      910 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying 「cotton」 shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html> 0.9244136988525732
      906 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown 「cotton」 construction. Shields against sun and precipitation. Metallic ions in the fibers inhibit bacterial growth, and help neutralize odor.</span></p></body></html> 0.9134171046194403
      909 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% 「cotton」/20% polyester blend makes it easy to keep them clean.</span></p></body></html> 0.8856420222728282
    2. クエリ 2:

      SELECT ID, Description 
         FROM MarketingInformation1
         WHERE CONTAINS( Description, 'cotton -visor' );
      ID Description
      908 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown 「cotton」 hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage.</span></p></body></html>
      909 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% 「cotton」/20% polyester blend makes it easy to keep them clean.</span></p></body></html>
      910 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying 「cotton」 shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html>
    3. 次の文は、cotton という単語について各ローをテストします。ローにこの単語が含まれている場合は Results カラムに 1 が表示され、含まれていない場合は 0 が返されます。

      SELECT ID, Description, IF CONTAINS ( Description, 'cotton' )
            THEN 1
            ELSE 0
            ENDIF AS Results
         FROM MarketingInformation1;
      ID Description Results
      901 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>We've improved the design of this perennial favorite. A sleek and technical shirt built for the trail, track, or sidewalk. UPF rating of 50+.</span></p></body></html> 0
      902 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This simple, sleek, and lightweight technical shirt is designed for high-intensity workouts in hot and humid weather. The recycled polyester fabric is gentle on the earth and soft against your skin.</span></p></body></html> 0
      903 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Tee Shirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A sporty, casual shirt made of recycled water bottles. It will serve you equally well on trails or around town. The fabric has a wicking finish to pull perspiration away from your skin.</span></p></body></html> 0
      904 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>This fashionable hat is ideal for glacier travel, sea-kayaking, and hiking. With concealed draw cord for windy days.</span></p></body></html> 0
      905 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Baseball Cap</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A lightweight wool cap with mesh side vents for breathable comfort during aerobic activities. Moisture-absorbing headband liner.</span></p></body></html> 0
      906 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown 「cotton」 construction. Shields against sun and precipitation. Metallic ions in the fibers inhibit bacterial growth, and help neutralize odor.</span></p></body></html> 1
      907 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Visor</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>A polycarbonate visor with an abrasion-resistant coating on the outside. Great for jogging in the spring, summer, and early fall. The elastic headband has plenty of stretch to give you a snug yet comfortable fit every time you wear it.</span></p></body></html> 0
      908 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Lightweight 100% organically grown 「cotton」 hooded sweatshirt with taped neck seams. Comes pre-washed for softness and to lessen shrinkage.</span></p></body></html> 1
      909 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Sweatshirt</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>Top-notch construction includes durable topstitched seams for strength with low-bulk, resilient rib-knit collar, cuffs and bottom. An 80% 「cotton」/20% polyester blend makes it easy to keep them clean.</span></p></body></html> 1
      910 <html><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><title>Shorts</title></head><body lang=EN-US><p><span style='font-size:10.0pt;font-family:Arial'>These quick-drying 「cotton」 shorts provide all day comfort on or off the trails. Now with a more comfortable and stretchy fabric and an adjustable drawstring waist.</span></p></body></html> 1
参照