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

SQLite 3 NULLの場合、別の値を返す(ifnull())

概要

値がNULLかどうかを判定して、NULLだった場合別の値を返すようにするには ifnull()関数を使う。 ifnull()はOracleのNVL()に相当する機能。

構文

ifnull(value1, value2)
                

value1

NULLかどうかを判定する対象。この値がNULLでなければ関数はこの値をそのまま返す。

value2

value1がNULLだった場合、この関数はこの値を返す。

実行例１

環境

• Windows 10
• SQLite ver 3.45.1

sqlite> .mode box
sqlite> -- 1.
sqlite> select ifnull(null, 'sample1') as result;
┌─────────┐
│ result  │
├─────────┤
│ sample1 │
└─────────┘

sqlite> -- 2.
sqlite> select ifnull(0, 'sample2') as result;
┌────────┐
│ result │
├────────┤
│ 0      │
└────────┘
sqlite> select ifnull('not null', 'sample3') as result;
┌──────────┐
│  result  │
├──────────┤
│ not null │
└──────────┘

sqlite> -- 3.
sqlite> .nullvalue [null]
sqlite> select ifnull(null, null) as result;
┌────────┐
│ result │
├────────┤
│ [null] │
└────────┘

                

1. 第一引数がnullだった場合、第二引数の値を返す。
2. 第一引数がnullでなければ、第一引数をそのまま返す。
3. 第一引数・第二引数ともにnullだった場合、nullを返す。

実行例２

users

id	last_login	created_at
0001	2024-02-01	2024-01-31
0002		2024-02-01
0003		2024-02-03

そのまま出力。

select
    id
    , last_login
    , created_at
from
    users
;
                    

結果

id	last_login	created_at
0001	2024-02-01	2024-01-31
0002		2024-02-01
0003		2024-02-03

last_login がNULLだった場合、「not logged in」を表示する。

select
    id
    , ifnull(last_login, 'not logged in') as last_login
from
    users
;
                    

結果

id	last_login
0001	2024-02-01
0002	not logged in
0003	not logged in

last_login がNULLだった場合、created_at を換わりに取得する。

select
    id
    , ifnull(last_login, created_at) as last_login
from
    users
;
                    

結果

id	last_login
0001	2024-02-01
0002	2024-02-01
0003	2024-02-03

参考URL

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

SQLite 3 コマンドラインツール起動時に読み取り専用でデータベースを開く (-readonly)

概要

SQLiteにはGRANTやREVOKEといった権限を操作するコマンドはなく、 ユーザやアクセス権限はOSのファイルアクセス管理に準ずる。

• SQL Features That SQLite Does Not Implement

ただし、プログラミング言語用のライブラリやコマンドラインツールには データベースファイルを読み取り専用で開く手段が準備されていることがある。

このページでは、コマンドプロンプトやbashといったシェルから SQLite 3 のデータベースファイルを読み取り専用で開く方法を記載しておく。

コマンド

SQLite 3 のコマンドのオプションに「-readonly」を指定してデータベースを開くことで 読み取り専用で開くことが出来る。
読み取り専用で開くとinsert や update 実行時にエラーになる。

sqlite3 -readonly DB_NAME
                

実行例

環境

• Windows 10 64bit
• SQLite3 (3.43.1) Command-Line Shell

C:\temp>rem # 1.
C:\temp>sqlite3 -readonly test.db
SQLite version 3.45.1 2024-01-30 16:01:20 (UTF-16 console I/O)
Enter ".help" for usage hints.

sqlite> -- # 2.
sqlite> create table test_table (id, name);
Runtime error: attempt to write a readonly database (8)
sqlite>
                

• 読み取り専用でtest.dbを開く
• create文を実行しようとするとエラーになるのがわかる

SQLite 3 のコマンドラインツールを起動した後でも 「.open」コマンドを使用して読み取り専用でデータベースに繋ぎなおすことができる。

• SQLite 3 読み取り専用でデータベースを開く (.open --readonly)

参考URL

• SQL Features That SQLite Does Not Implement
  公式の未実装機能に関するドキュメント
  https://www.sqlite.org/omitted.html