開発者ドキュメント

SQLでワイルドカードを使用する方法

序章

多くのコンピュータ言語と同様に、SQLではさまざまなワイルドカード文字を使用できます。 ワイルドカードは、1つ以上の他の文字または値を表すことができる特別なプレースホルダー文字です。 これはSQLの便利な機能であり、データベース内に保持されている正確な値を知らなくても、データベースでデータを検索できます。

このガイドでは、SQLで指定されたワイルドカードを使用してデータをクエリする方法について説明します。

前提条件

このガイドに従うには、SQLを使用するある種のリレーショナルデータベース管理システム(RDBMS)を実行しているコンピューターが必要です。 このガイドの手順と例は、次の環境を使用して検証されています。

:多くのRDBMSは、独自のSQL実装を使用していることに注意してください。 このチュートリアルで概説されているコマンドはほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストした場合、正確な構文または出力が異なる場合があります。

また、ワイルドカードを使用して練習できるサンプルデータがロードされたデータベースとテーブルも必要です。 これがない場合は、次の MySQLへの接続とサンプルデータベースのセットアップセクションを読んで、このガイドで例全体で使用するデータベースとテーブルを作成する方法の詳細を確認してください。

このページに埋め込まれているインタラクティブ端末を使用して、このチュートリアルのサンプルクエリを試すこともできます。 次のLaunch an Interactive Terminal!ボタンをクリックして開始します。

MySQLへの接続とサンプルデータベースの設定

SQLデータベースシステムがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します。

  1. ssh sammy@your_server_ip

次に、MySQLサーバープロンプトを開き、sammyをMySQLユーザーアカウントの名前に置き換えます。 このページに埋め込まれたインタラクティブ端末を使用している場合、プロンプトが表示されたときに使用するパスワードはsecretという単語であることに注意してください。

  1. mysql -u sammy -p

wildcardsDBという名前のデータベースを作成します。

  1. CREATE DATABASE wildcardsDB;

データベースが正常に作成されると、次のような出力が表示されます。

Output
Query OK, 1 row affected (0.01 sec)

wildcardsDBデータベースを選択するには、次のUSEステートメントを実行します。

  1. USE wildcardsDB;
Output
Database changed

データベースを選択したら、次のコマンドを使用してデータベース内にテーブルを作成できます。 例として、アプリケーションのユーザーのプロファイル情報を保持するuser_profilesという名前のテーブルを作成するとします。 このテーブルには、次の5つの列が含まれます。

次のコマンドを実行して、このサンプルテーブルを作成します。

  1. CREATE TABLE user_profiles (
  2. user_id int,
  3. name varchar(30),
  4. email varchar(40),
  5. birthdate date,
  6. quote varchar(300),
  7. PRIMARY KEY (user_id)
  8. );
Output
Database changed

次に、いくつかのサンプルデータを空のテーブルに挿入します。

  1. INSERT INTO user_profiles
  2. VALUES
  3. (1, 'Kim', 'bd_eyes@example.com', '1945-07-20', '"Never let the fear of striking out keep you from playing the game." -Babe Ruth'),
  4. (2, 'Ann', 'cantstandrain@example.com', '1947-04-27', '"The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt'),
  5. (3, 'Phoebe', 'poetry_man@example.com', '1950-07-17', '"100% of the people who give 110% do not understand math." -Demitri Martin'),
  6. (4, 'Jim', 'u_f_o@example.com', '1940-08-13', '"Whoever is happy will make others happy too." -Anne Frank'),
  7. (5, 'Timi', 'big_voice@example.com', '1940-08-04', '"It is better to fail in originality than to succeed in imitation." -Herman Melville'),
  8. (6, 'Taeko', 'sunshower@example.com', '1953-11-28', '"You miss 100% of the shots you don\'t take.
  9. " -Wayne Gretzky'), (7, 'Irma', 'soulqueen_NOLA@example.com', '1941-02-18', '"
  10. You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose.
  11. " -Dr. Seuss'), (8, 'Iris', 'our_town@example.com', '1961-01-05', '"
  12. You will face many defeats in life, but never let yourself be defeated." -Maya Angelou');
Output
Query OK, 8 rows affected (0.00 sec) Records: 8 Duplicates: 0 Warnings: 0

これで、ガイドの残りの部分に従い、ワイルドカードを使用してSQLでデータをクエリする方法について学習する準備が整いました。

ワイルドカードを使用したデータのクエリ

はじめにで述べたように、ワイルドカードは、1つ以上の他の文字または値を表すことができる特別なプレースホルダー文字です。

SQLでは、定義されているワイルドカード文字は2つだけです。

これらのワイルドカードは、LIKEまたはNOT LIKE演算子を使用したクエリのWHERE句でのみ使用されます。

前提条件セクションのサンプルデータの使用を説明するために、user_profilesテーブルにリストされているユーザーの少なくとも1人が、3文字の長さで、「im」で終わる名前を持っていることを知っているとしましょう。彼らが誰であるかを確認してください。 これらのユーザーの名前の最初の文字がわからないため、_ワイルドカードを使用して次のクエリを実行し、ユーザーが誰であるかを見つけることができます。

  1. SELECT * FROM user_profiles WHERE name LIKE '_im';
Output
+---------+------+---------------------+------------+---------------------------------------------------------------------------------+ | user_id | name | email | birthdate | quote | +---------+------+---------------------+------------+---------------------------------------------------------------------------------+ | 1 | Kim | bd_eyes@example.com | 1945-07-20 | "Never let the fear of striking out keep you from playing the game." -Babe Ruth | | 4 | Jim | u_f_o@example.com | 1940-08-13 | "Whoever is happy will make others happy too." -Anne Frank | +---------+------+---------------------+------------+---------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

:この例では、SELECTの後にアスタリスク(*)が続きます。 これはSQLの省略形であり、「すべての列」を表します。

特定のアプリケーションやプログラミング言語、およびSQLの一部の実装では、この例で使用されているパーセント記号のように、アスタリスクが0個以上の文字を表すワイルドカード文字として使用されます。 ただし、上記の例のアスタリスクは、1つ以上の不明な文字ではなく、特定の何か(つまり、user_profilesテーブルのすべての列)を表すため、ワイルドカードではありません。

NOT LIKE演算子には、LIKEとは逆の効果があります。 ワイルドカードパターンに一致するすべてのレコードを返すのではなく、パターンに一致しないすべての行を返します。 説明のために、前のクエリを再度実行しますが、LIKENOT LIKEに置き換えます。

  1. SELECT * FROM user_profiles WHERE name NOT LIKE '_im';

今回は、name列の値が_imと一致するすべての行が結果セットから省略されます。

Output
+---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ | user_id | name | email | birthdate | quote | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ | 2 | Ann | cantstandrain@example.com | 1947-04-27 | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt | | 3 | Phoebe | poetry_man@example.com | 1950-07-17 | "100% of the people who give 110% do not understand math." -Demitri Martin | | 5 | Timi | big_voice@example.com | 1940-08-04 | "It is better to fail in originality than to succeed in imitation." -Herman Melville | | 6 | Taeko | sunshower@example.com | 1953-11-28 | "You miss 100% of the shots you don't take." -Wayne Gretzky | | 7 | Irma | soulqueen_NOLA@example.com | 1941-02-18 | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss | | 8 | Iris | our_town@example.com | 1961-01-05 | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou | +---------+--------+----------------------------+------------+--------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)

別の例として、データベースにリストされているユーザーの何人かが「I」で始まる名前を持っているが、すべてを覚えているわけではないことを知っているとします。 次のクエリに示すように、%ワイルドカードを使用して、それらすべてを一覧表示できます。

  1. SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'I%';
Output
+---------+------+----------------------------+ | user_id | name | email | +---------+------+----------------------------+ | 7 | Irma | soulqueen_NOLA@example.com | | 8 | Iris | our_town@example.com | +---------+------+----------------------------+ 2 rows in set (0.00 sec)

MySQLでは、デフォルトでは、LIKEおよびNOT LIKE演算子は大文字と小文字を区別しないことに注意してください。 これは、ワイルドカードパターンで「I」を大文字にしない場合でも、前のクエリが同じ結果を返すことを意味します。

  1. SELECT user_id, name, email FROM user_profiles WHERE name LIKE 'i%';
Output
+---------+------+----------------------------+ | user_id | name | email | +---------+------+----------------------------+ | 7 | Irma | soulqueen_NOLA@example.com | | 8 | Iris | our_town@example.com | +---------+------+----------------------------+ 2 rows in set (0.00 sec)

ワイルドカードは正規表現とは異なることに注意してください。 一般に、ワイルドカードはグロブスタイルのパターンマッチングで使用される文字を指しますが、正規表現は正規言語に依存して文字列パターンをマッチングします。

ワイルドカード文字のエスケープ

SQLのワイルドカード文字の1つを含むデータエントリを検索したい場合があります。 このような場合、エスケープ文字を使用して、%または_のワイルドカード関数を無視し、代わりにプレーンテキストとして解釈するようにSQLに指示できます。

たとえば、データベースにリストされているユーザーの少なくとも2人が、パーセント記号を含むお気に入りの引用符を持っているが、彼らが誰であるかわからないとします。

次のクエリを実行してみてください。

  1. SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%';

ただし、このクエリはあまり役に立ちません。 パーセント記号は、任意の長さの任意の文字列の代用として機能するため、テーブル内のすべての行を返します。

Output
+---------+--------+--------------------------------------------------------------------------------------------------------------------------+ | user_id | name | quote | +---------+--------+--------------------------------------------------------------------------------------------------------------------------+ | 1 | Kim | "Never let the fear of striking out keep you from playing the game." -Babe Ruth | | 2 | Ann | "The future belongs to those who believe in the beauty of their dreams." -Eleanor Roosevelt | | 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin | | 4 | Jim | "Whoever is happy will make others happy too." -Anne Frank | | 5 | Timi | "It is better to fail in originality than to succeed in imitation." -Herman Melville | | 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky | | 7 | Irma | "You have brains in your head. You have feet in your shoes. You can steer yourself any direction you choose." -Dr. Seuss | | 8 | Iris | "You will face many defeats in life, but never let yourself be defeated." -Maya Angelou | +---------+--------+--------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec)

パーセント記号をエスケープするには、MySQLのデフォルトのエスケープ文字であるバックスラッシュ(\)を前に付けることができます。

  1. SELECT * FROM user_profiles WHERE quote LIKE '\%';

ただし、このクエリは、quote列の内容がのみでパーセント記号で構成されている必要があることを指定しているため、役に立ちません。 したがって、結果セットは空になります。

Output
Empty set (0.00 sec)

これを修正するには、LIKE演算子に続く検索パターンの最初と最後に、パーセント記号のワイルドカードを含める必要があります。

  1. SELECT user_id, name, quote FROM user_profiles WHERE quote LIKE '%\%%';
Output
+---------+--------+----------------------------------------------------------------------------+ | user_id | name | quote | +---------+--------+----------------------------------------------------------------------------+ | 3 | Phoebe | "100% of the people who give 110% do not understand math." -Demitri Martin | | 6 | Taeko | "You miss 100% of the shots you don't take." -Wayne Gretzky | +---------+--------+----------------------------------------------------------------------------+ 2 rows in set (0.00 sec)

このクエリでは、バックスラッシュは2番目のパーセント記号のみをエスケープしますが、1番目と3番目の記号は引き続きワイルドカードとして機能します。 したがって、このクエリは、quote列に少なくとも1つのパーセント記号が含まれているすべての行を返します。

次の例のように、ESCAPE句を使用してカスタムエスケープ文字を定義することもできることに注意してください。

  1. SELECT user_id, name, email FROM user_profiles WHERE email LIKE '%@_%' ESCAPE '@';
Output
+---------+--------+----------------------------+ | user_id | name | email | +---------+--------+----------------------------+ | 1 | Kim | bd_eyes@example.com | | 3 | Phoebe | poetry_man@example.com | | 4 | Jim | u_f_o@example.com | | 5 | Timi | big_voice@example.com | | 7 | Irma | soulqueen_NOLA@example.com | +---------+--------+----------------------------+ 5 rows in set (0.00 sec)

このクエリは、@記号をエスケープ文字として定義し、email列に少なくとも1つのアンダースコアが含まれているすべての行を返します。 ESCAPE句を削除すると、各行に少なくとも1つの@記号が含まれているため、クエリはテーブルからすべての行を返します。

結論

このガイドを読むことで、ワイルドカードSQLベースのデータベースを使用およびエスケープする方法を学びました。 ここで概説するコマンドは、SQLを使用するすべてのデータベース管理システムで機能するはずです。 すべてのSQLデータベースは独自の言語実装を使用しているため、各コマンドとそのオプションの完全なセットの詳細については、DBMSの公式ドキュメントを参照してください。

SQLの操作について詳しく知りたい場合は、SQLの使用方法に関するこのシリーズの他のチュートリアルを確認することをお勧めします。

モバイルバージョンを終了