SQLite3 ソートしたときのNULLの位置を調整する

概要

NULLを含むデータをソートした場合、 デフォルトだとNULLは最小値より小さい値としてソートされる。
nulls first、もしくは nulls last を使用することで、 NULLの位置をコントロールすることができる。

構文

NULL を最初に配置する。

order by column_name nulls first
                

NULL を最後に配置する。

order by column_name nulls last
                

実行例

環境

• Windows 10 64bit
• SQLite Command-Line Shell ver 3.50.4

以下のテーブルからデータをソートして取得する。

id	name
0	hoge1
1	hoge2
2

昇順ソートでNULLの位置を調整する

nameカラムに対し、

1. 何も指定しない場合
2. ascだけを指定した場合
3. nulls firstを指定した場合、 ascだけを指定した場合と同じ結果になる
4. ascとnulls first は一緒に指定することもできる（結果は同じ）
5. nulls lastを指定した場合、 値が入っているレコードが先に来て、nullが最後に来る

sqlite> .nullvalue [null]
sqlite> .mode box
sqlite> -- 1.
sqlite> select * from user;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
sqlite> -- 2.
sqlite> select * from user order by name asc;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 3.
sqlite> select * from user order by name nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 4.
sqlite> select * from user order by name asc nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 0  │ hoge1  │
│ 1  │ hoge2  │
└────┴────────┘
sqlite> -- 5.
sqlite> select * from user order by name asc nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
sqlite> select * from user order by name nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 0  │ hoge1  │
│ 1  │ hoge2  │
│ 2  │ [null] │
└────┴────────┘
                    

降順ソートでNULLの位置を調整する

nameカラムに対し、

1. descだけを指定した場合
2. desc nulls firstを指定した場合、 値が入っているレコードが後に来て、nullが最初に来る
3. desc nulls lastを指定した場合、 descを単独で指定した場合と同じ結果になる

sqlite> -- 1.
sqlite> select * from user order by name desc;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 1  │ hoge2  │
│ 0  │ hoge1  │
│ 2  │ [null] │
└────┴────────┘
sqlite> -- 2.
sqlite> select * from user order by name desc nulls first;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 2  │ [null] │
│ 1  │ hoge2  │
│ 0  │ hoge1  │
└────┴────────┘
sqlite> -- 3.
sqlite> select * from user order by name desc nulls last;
┌────┬────────┐
│ id │  name  │
├────┼────────┤
│ 1  │ hoge2  │
│ 0  │ hoge1  │
│ 2  │ [null] │
└────┴────────┘
                    

参考URL

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite ORDER BY に関するドキュメント
  https://www.sqlite.org/syntax/ordering-term.html

SQLite3 エラーが起きた時に処理をキャンセルする (.bail)

概要

SQLite3のコマンドラインツールを使用している際、 一連の処理でエラーになった場合、 .bailコマンドを設定しておくことで その後の処理をキャンすることができる。

SQLiteコマンドラインツールを起動する際に -bailオプションを指定することでも 同様の効果を得ることができる。

これにより、連鎖的にエラーが発生したり、意味のない処理が動くことを止めることができる。

構文

コマンドラインツール起動中にBail機能をON/OFF にする。（デフォルトはOFF）

.bail on
.bail off
                

コマンドライン起動時にBail機能をON/OFF にする。（デフォルトはOFF）

c:\temp> sqlite3 -bail on
c:\temp> sqlite3 -bail off
                

実行例

環境

• Windows 10 64bit
• SQLite Command-Line Shell ver 3.50.2

.bailはCLIでコマンドを手入力する作業ではあまり恩恵がない。（と思う）
外部のSQLファイルを読み込んだり、シェルのコマンドラインからSQLを渡したときに効果を発揮する。

.bailがOFFのままの場合

以下のようなSQLファイルを準備する。

sample_01.sql

-- # 1
.bail off

-- # 2
create table user_01 (id, name);
create tbl user_02 (id, name);
create table user_03 (id, name);
                    

1. bailをOFFにする
2. create table文を３つ実行する。
   ただし、2つ目のSQL構文に誤りがあるのでエラーになるようになっている。

準備したSQLファイルをSQLite3のコマンドラインツールから読み込んで テーブル一覧を表示してみる。

.read ./sample_01.sql
.tables
                    

• SQLite3 コマンドラインツールからSQLファイルを読み込んで実行する
• SQLite3 コマンドラインツールでテーブル一覧を表示する (.tables)

実行結果

sqlite> .read ./sample.sql
Parse error near line 6: near "tbl": syntax error
  create tbl user_02 (id, name);
         ^--- error here
sqlite> .tables
user_01  user_03
                    

２つ目のSQLが失敗し、エラーになっている。 １つ目のSQLと３つ目のSQLは成功し、 テーブルが２つ作成されていることがわかる。

.bailをONにした場合

以下のようなSQLファイルを準備する。

sample_02.sql

-- # 1
.bail on

-- # 2
create table user_01 (id, name);
create tbl user_02 (id, name);
create table user_03 (id, name);
                    

1. bailをONにする
2. create table文を３つ実行する。
   2つ目のSQL構文に誤りがあるのでエラーになるようになっている。
3. テーブル一覧を表示する

準備したSQLファイルをSQLite3のコマンドラインツールから読み込む。

.read ./sample_02.sql
.tables
                    

• SQLite3 コマンドラインツールからSQLファイルを読み込んで実行する
• SQLite3 コマンドラインツールでテーブル一覧を表示する (.tables)

実行結果

sqlite> .read ./sample02.sql
Parse error near line 6: near "tbl": syntax error
  create tbl user_02 (id, name);
         ^--- error here
sqlite> .tables
user_01
                    

２つ目のSQLが失敗し、.tablesを実行すると、 user_03テーブルが作成されていないことがわかる。

よって２つ目でSQLが失敗した後、以降のSQLやコマンドはキャンセルされていることがわかる。

コマンドラインツールで手で入力する場合

以下のような実行の仕方では bailは意味がない。

sqlite> .bail on
sqlite> create table user_01 (id, name);
sqlite> create tbl user_02 (id, name);
Parse error: near "tbl": syntax error
  create tbl user_02 (id, name);
         ^--- error here
sqlite> create table user_03 (id, name);
sqlite> .tables
user_01  user_03
                    

以下のように一度に複数のSQLを実行する場合であれば、 途中で発生したエラー以降はキャンセルされる。

sqlite> .bail on
sqlite> create table user_01 (id, name); create tbl user_02 (id, name); create table user_03 (id, name);
Parse error: near "tbl": syntax error
  create tbl user_02 (id, name); create table user_03 (id, name);
         ^--- error here
sqlite> .tables
user_01
                    

参考URL

• Command Line Shell For SQLite
  公式のコマンドラインツールに関するドキュメント
  https://www.sqlite.org/cli.html

SQLite 3 文字列から特定の文字列があるかを確認する、 特定の文字列が何文字目から始まるかを調べる。(instr())

概要

isstr() を使うと ある文字列の中に含まれる指定した文字列の開始位置を取得できる。
文字列が含まれていない場合、関数は0 を返す。

構文

instr(str1, str2)
                

str1

調査対象の文字列

str2

str1に含まれているであろう文字列

実行例１

環境

• Windows 10
• SQLite ver 3.50.1

sqlite> --# 1.                    
sqlite> select instr('sample@example.com', 'hoge');
0
sqlite> --# 2.
sqlite> select instr('sample@example.com', 'sample');
1
sqlite> --# 3.
sqlite> select instr('sample@example.com', 'example');
8
sqlite> --# 4.
sqlite> select instr('sample@example.com', 'am');
2
sqlite> --# 5.
sqlite> .nullvalue [null]
sqlite> select instr(NULL, 'example');
[null]
sqlite> select instr('sample@example.com', NULL);
[null]
sqlite> select instr(NULL, NULL);
[null]
                

1. 対象の文字列が含まれていない場合は 0 を返す
2. 先頭で文字が見つかった場合は 1 となる
3. 見つかった文字列の開始位置を返す
4. 複数個所に検索文字列がある場合、最初に見つかった文字列の位置を返す
5. 引数のどちらかがNULLの場合、 NULLを返す。

参考URL

• 公式のinstr()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#instr

SQLite 3 二つの値を比較し同じ場合NULLを返す(nullif())

概要

nullif()は引数に与えられた2つの値を比較し、 同じだった場合null、違った場合第一引数を返す。
iif()の亜種のような関数。

構文

nullif(value1, value2)
                

value1

二つの引数の値が異なる場合、この引数が返される。

value2

value1 と比較する値

実行例１

環境

• Windows 10
• SQLite ver 3.45.1

sqlite> .nullvalue [null]
sqlite> select nullif(1, 1);
[null]
sqlite> select nullif(1, 0);
1
sqlite> select nullif('sample1', 'sample1');
[null]
sqlite> select nullif('sample1', 'sample2');
sample1
sqlite> select nullif('1', 1);
1
sqlite> select nullif(true, true);
[null]
sqlite> select nullif(true, false);
1
                

参考URL

• 公式のnullif()に関するドキュメント
  https://www.sqlite.org/lang_corefunc.html#nullif

• SQLite 3 NULLの場合、別の値を返す(ifnull())
  https://sfnovicenotes.blogspot.com/2024/03/sqlite-3-nullifnull.html