SQLitのパターンマッチング編
【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力
【 LIKE 句】
カラムの値と文字列とが一致するかどうかを比較するときに % と _ の2つの特殊文字を使ったパターンマッチングを行う事が出来る。
書式
SELECT カラム名 , ... FROM テーブル名 WHERE カラム LIKE パターン;
実行
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> select * from user;
id name address
---------- ---------- ----------
1 Suzuki Osaka
2 Yamada Tokyo
3 Kudou Chiba
4 Ushijima Nagoya
5 Hanada Saitama
6 Okamoto Sapporo
7 Shima Tokyo
sqlite>
sqlite> select * from user where address like 'S%';
id name address
---------- ---------- ----------
5 Hanada Saitama
6 Okamoto Sapporo
sqlite>
・address カラムの値が o という文字が2回出てくる文字列と一致するデータ取得
sqlite> select * from user where address like '%o%o%';
id name address
---------- ---------- ----------
2 Yamada Tokyo
6 Okamoto Sapporo
7 Shima Tokyo
sqlite>
・name カラムの値が5文字のデータ取得
sqlite> select * from user where name like '_____';
id name address
---------- ---------- ----------
3 Kudou Chiba
7 Shima Tokyo
sqlite>
・NOT 演算子と組み合わせて name カラムの値が5文字ではないデータ取得
sqlite> select * from user where name not like '_____';
id name address
---------- ---------- ----------
1 Suzuki Osaka
2 Yamada Tokyo
4 Ushijima Nagoya
5 Hanada Saitama
6 Okamoto Sapporo
sqlite>
■特殊文字のエスケープ方法
LIKE句の中で % と _ は特別な意味を持ちますが、特殊な文字ではなく文字の一つとしてこの2つの文字を使用したい場合にはエスケープ処理をする必要があります
書式
SELECT カラム名 , ... FROM テーブル名
WHERE カラム LIKE パターン ESCAPE エスケープ文字;
例文
sqlite> create table foods(id integer, name text);
sqlite> insert into foods values(1, 'Water');
sqlite> insert into foods values(2, 'Apple_Pie');
sqlite> insert into foods values(3, 'Black_Coffee');
sqlite> insert into foods values(4, 'Pizza');
sqlite> insert into foods values(5, 'Sandwich');
sqlite> insert into foods values(6, 'French_Bread');
sqlite>
・name カラムの値に _ が含まれているデータ取得
sqlite> select * from foods where name like '%¥_%' escape '¥';
id name
---------- ----------
2 Apple_Pie
3 Black_Coff
6 French_Bre
sqlite>
【GLOB句】
カラムの値と文字列とが一致するかどうかを比較するときに * と ? の2つの特殊文字を使ったパターンマッチングを行う事が出来る。
書式
SELECT カラム名 , ... FROM テーブル名 WHERE カラム GLOB パターン;
パターン［特殊文字］
* 任意の0文字以上の文字列
? 任意の1文字
[abc] a or b or cのいずれかに一致
[a-d] aからdまでにいずれかに一致
実行
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table user1(id integer, screenname text);
sqlite>
sqlite> insert into user1 values(1, 'Blogger');
sqlite> insert into user1 values(2, 'rondobell');
sqlite> insert into user1 values(3, 'blue*star');
sqlite> insert into user1 values(4, '2525');
sqlite> insert into user1 values(5, 'Star');
sqlite>
sqlite> .header on
sqlite> .mode column
sqlite> select * from user1;
id screenname
---------- ----------
1 Blogger
2 rondobell
3 blue*star
4 2525
5 Star
sqlite>
■ screenname カラムの値が b からはじまり任意の文字列とマッチするデータ取得
sqlite> select * from user1 where screenname glob 'b*';
id screenname
---------- ----------
3 blue*star
sqlite>
■大文字と小文字を区別しますので b または B で始まる文字列とマッチさせたい場合は次のようにパターンを記述する
sqlite> select * from user1 where screenname glob '[bB]*';
id screenname
---------- ----------
1 Blogger
3 blue*star
sqlite>
■screenname カラムの値が 半角英文字( a から z 、または A から Z )で始まり任意の文字列とマッチするデータ取得
sqlite> select * from user1 where screenname glob '[a-zA-Z]*';
id screenname
---------- ----------
1 Blogger
2 rondobell
3 blue*star
5 Star
sqlite>
［特殊文字のエスケープ方法］
GLOB 句の中でアスタリスク(*)、クエスチョンマーク(?)、左括弧([)、右括弧(])の4つの文字は特別な意味を持ちますが、特殊な文字ではなく文字の一つとしてこの4つの文字を使用したい場合にはエスケープ処理をする必要があります。
sqlite> select * from user1 where screenname glob '*[*]*';
id screenname
---------- ----------
3 blue*star
sqlite>
