SQLite3 JSON要素の型を調べる(json_type())

概要

json_type関数を使うと要素の型を調べることが出来る。
判定出来る型は以下の通り。

型名	意味	例
object	連想配列	{ "string_01" : [ 0, 1, 2, 3] , "string_02" : null }
array	配列	[ 0, 1, 2, 3 ]
integer	整数	10
real	浮動小数点	3.14
true	真	true
false	偽	false
null	NULL値	null
text	文字列	"This is Text."

判定できなかった場合はエラーになる

構文

json_type(json_string, json_path)
                

• SQLite3で使えるJSON Path

実行例

環境

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

sqlite> -- # 1.
sqlite> select json_type('
'  ...>     {
'  ...>         "string_01"   : [ 0, 1, 2, 3]
'  ...>         , "string_02" : null
'  ...>     }
'  ...> ');
object

sqlite> -- # 2.
sqlite> select json_type('
'  ...>     [ 0, 1, 2, 3 ]
'  ...> ');
array

sqlite> -- # 3.
sqlite> select json_type('10');
integer

sqlite> -- # 4.
sqlite> select json_type('3.14');
real

sqlite> -- # 5.
sqlite> select json_type('true');
true

sqlite> -- # 6.
sqlite> select json_type('false');
false

sqlite> -- # 7.
sqlite> select json_type('null');
null

sqlite> -- # 8.
sqlite> select json_type('"This is Text"');
text

                    

1. いわゆる連想配列は「object」として判定される
2. 通常の配列の場合は「array」
3. 整数は「integer」
4. 浮動小数点は「real」
5. 真偽値のtrueは「true」
6. 真偽値のfalseは「false」
7. nullは「null」
8. 文字列は「text」

なお、json_type()の返り値自体は文字列型となっている。

JSON型以外を引数に指定すると以下のような結果になる

sqlite> .nullvalue [null]
sqlite> -- # 1.
sqlite> select json_type('This is Text');
Runtime error: malformed JSON

sqlite> -- # 2.
sqlite> select json_type(10);
integer

sqlite> -- # 3.
sqlite> select json_type(3.14);
real

sqlite> -- # 4.
sqlite> select json_type(true);
integer

sqlite> select json_type(false);
integer

sqlite> -- # 5.
sqlite> select json_type(null);
[null]
                    

1. JSON形式になっていない場合はエラーとなる
2. 文字列ではなく素の整数でもintegerとして判定される
3. 文字列ではなく素の小数点でもrealとして判定される
4. trueやbooleanは数値型として判定される
5. nullはnullが返り値となる

• SQLite 3 trueとfalseの取り扱い

参考URL

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

SQLite3 大文字を小文字に変換する (lower())

概要

文字列中の英字の大文字を小文字に変換するにはlower関数を使う。
半角英字のみ有効で、全角英字には効果がないので注意すること。

構文

lower(string)
                

実行例

sqlite> -- # 1.
sqlite> select lower('ABCD');
abcd

sqlite> -- # 2.
sqlite> select lower('abcd');
abcd

sqlite> -- # 3.
sqlite> select lower('ABcd');
abcd

sqlite> -- # 4.
sqlite> select lower('ＡＢＣＤ');
ＡＢＣＤ
                

1. 大文字が小文字に変換される
2. 小文字のみの文字列に対しては効果がない
3. 小文字、大文字の両方が含まれる場合、大文字が小文字になる
4. 全角文字は変換されない

参考URL

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

SQLite3 小文字を大文字に変換する (upper())

概要

文字列中の英字の小文字を大文字に変換するにはupper関数を使う。
半角英字のみに有効で、全角英字には効果がないので注意すること。

構文

upper(string)
                

実行例

sqlite> -- # 1.
sqlite> select upper('abcd');
ABCD

sqlite> -- # 2.
sqlite> select upper('ABCD');
ABCD

sqlite> -- # 3.
sqlite> select upper('abCD');
ABCD

sqlite> -- # 4.
sqlite> select upper('ａｂｃｄ');
ａｂｃｄ
                

1. 小文字が大文字に変換される
2. 大文字のみの文字列に対しては効果がない
3. 小文字、大文字の両方が含まれる場合、小文字が大文字になる
4. 全角文字は変換されない

参考URL

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

SQLite3 前方、後方一致した部分を削除する(trim())

概要

前方、後方一致した部分を削除するにはtrimを利用する。

trimは ltrimとrtrimを同時に使ったような関数になる。
それぞれの関数については以下の記事参照のこと。

• SQLite3 前方一致した部分を削除する(ltrim())
• SQLite3 後方一致した部分を削除する(rtrim())

ある文字列と文字列を比較して一致した部分を削除する、という機能なので 使い方を理解すれば結構便利。

構文

trim(string1)
trim(string1, string2)
                

第２引数を指定しないと第１引数の前後の半角空白が削除される。
(※全角の空白は削除されないので注意)

実行例

sqlite> select trim('abcdefg', '');
abcdefg

sqlite> select trim('abcd');
abcd

sqlite> -- # 1.
sqlite> select trim('    abcd');
abcd

sqlite> -- # 2.
sqlite> select trim('abcd    ');
abcd

sqlite> -- # 3.
sqlite> select trim('    abcd    ');
abcd

sqlite> -- # 4.
sqlite> select trim('abcd', 'ab');
cd

sqlite> -- # 5.
sqlite> select trim('abcd', 'cd');
ab

sqlite> -- # 6.
sqlite> select trim('abcd', 'bc');
abcd

sqlite> -- # 7.
sqlite> select trim('abcd', 'abxy');
cd

sqlite> -- # 8.
sqlite> select trim('abcd', 'xycd');
ab

sqlite> -- # 9.
sqlite> select trim('abcdef', 'abxyef');
cd

sqlite> -- # 10.
sqlite> select trim('defg', 'abcdefg');
                                        
                

1. 第２引数を省略すると半角空白が削除される(文字列の前方)
2. 第２引数を省略すると半角空白が削除される(文字列の後方)
3. 第２引数を省略すると半角空白が削除される(文字列の前後)
4. 前方一致している「ab」の部分が削除される
5. 後方一致している「cd」の部分が削除される
6. 前方・後方一致ではないので削除されない
7. 前方一致している「ab」の部分のみが削除される
8. 後方一致している「cd」の部分のみが削除される
9. 前方・後方一致している「ab」「cd」がそれぞれ削除される
10. 前方・後方一致している部分は完全に削除される

参考URL

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

• あさはか備忘録: SQLite3 前方一致した部分を削除する(ltrim())
  https://sfnovicenotes.blogspot.com/2022/07/sqlite3-ltrim.html
• あさはか備忘録: SQLite3 後方一致した部分を削除する(rtrim())
  https://sfnovicenotes.blogspot.com/2022/08/sqlite3-rtrim.html

SQLite3 CSVデータを取り込む、CSVデータを出力する(～ver 3.31.1)

概要

テキストデータであるCSVファイルをSQLite3に入力/出力させる方法。
コマンドラインツールから利用する。
自作のスクリプトでテキストを読込/出力したりDBに接続してinsert/selectをやるよりはるかに簡単。
取り込む処理をインポート/バルクロード、出力する処理をエクスポートと言ったりする。

Update!

SQLiteのver 3.32.0 からは「.import」コマンドにオプションが追加され、CSVファイルが取り込みやすくなった。 (「.mode」コマンドでCSVモードに変更しなくてもいい)

• SQLite3 CSVデータを取り込む、CSVデータを出力する (ver 3.32.0～)

CSVのフォーマットについて

CSVはRFCでフォーマットが定義されている。
RFCにはダブルクォートの取り扱いや改行コードなどについて記載されているので、一度は目を通しておくべき。
アプリケーションによって、対応していなかったり拡張されていたりするのでややこしい。

• Common Format and MIME Type for Comma-Separated Values (CSV) Files
  https://tools.ietf.org/html/rfc4180
• (日本語訳)
  http://www.kasai.fm/wiki/rfc4180jp

環境

• Windows 10 64bit
• SQLite3 ver.3.29.0

CSVファイルを取り込む

CSVファイルをインポートするに当たっては以下のことに注意する。

• CSVファイルは上記RFCに従って記述しておくのがベスト
• インポート先として指定した名前のテーブルが存在しているかどうかで挙動が異なる。

既にテーブルがある	CSVの一行目はデータとして登録される。 一行目が不要ならあらかじめ削除しておくこと。 テーブルのカラム数とデータ型にCSVを合わせる必要がある。 合っていない場合、エラーになる。
テーブルがない	指定したテーブル名で新しくテーブルが作られる。 CSVの一行目がカラム名になり、二行目以降がデータとして挿入される。 カラムのデータ型は全てTEXT型になる。

実行例

1. CSVモードに切り替え
2. sample.csv ファイルを sample_tableに挿入

sqlite> -- # 1.
sqlite> .mode csv
sqlite> -- # 2.
sqlite> .import sample.csv sample_table
                    

CSVファイルを書き出す

手順は以下の実行例を参照のこと。 SQLiteコマンドラインツールのCSVをエクスポート機能には、以下の特徴がある。

• RFCに従ってエスケープ処理をやってくれる。
• 出力先に同名のファイルが存在していても、上書き確認はしてくれない。
• 「.mode csv」 を利用し「.separator ,」は利用しないこと。
  「.separator ,」ではカンマやダブルクォーテーションがエスケープ処理されない。
  

実行例

1. CSVモードに切り替え
2. 次に打ち込むコマンドの結果をresult.csvに出力
3. 出力したいコマンドを実行する

sqlite> -- # 1. 
sqlite> .mode CSV
sqlite> -- # 2. 
sqlite> .once result.csv
sqlite> -- # 3.
sqlite> select * from sample_table;
                    

参考URL

• CSV Import
  公式のCSVインポートに関するドキュメント
  https://www.sqlite.org/cli.html#csv
• CSV Export
  公式のCSVエクスポートに関するドキュメント
  https://www.sqlite.org/cli.html#csvout

• あさはか備忘録: SQLite3 TSVデータを取り込む、TSVデータを出力する
  https://sfnovicenotes.blogspot.com/2019/10/sqlite3-tsvtsv.html