SQLite3 指定した文字を挟んで文字列を連結する (concat_ws(separator, X, Y, ...))

概要

文字列を連結（結合）するには||演算子か関数 concat() を使用するが、 カンマやドットなどの文字を挟んで連結する場合は concat_ws()を使用する。

ExcelのTEXTJOIN()関数と似たような感覚で使える。

ws は whitespace の略…か？

構文

concat_ws(separator, X, Y, ...)
                

実行例

環境

• Windows 11
• SQLite3 (3.53.1) Command-Line Shell

以下はカンマを使って文字列を連結する場合。
concat()でも同じ結果が得られるが、 concat_ws()の方が簡単に書ける。

sqlite> select concat_ws(',', 'Japan', 'Tokyo', 'Shibuya');
Japan,Tokyo,Shibuya
sqlite> select concat('Japan', ',', 'Tokyo', ',', 'Shibuya');
Japan,Tokyo,Shibuya
                

テーブルのカラムを連結する例:

sqlite> .mode box
sqlite> select * from user;
┌────┬────────────┬───────────┐
│ id │ first_name │ last_name │
├────┼────────────┼───────────┤
│ 1  │ taro       │ yamada    │
│ 2  │ jiro       │ yamada    │
│ 3  │ hanako     │ suzuki    │
└────┴────────────┴───────────┘
sqlite> select
   ...>     concat_ws('.', first_name, last_name)
   ...>         as full_name
   ...> from
   ...>     user;
┌───────────────┐
│   full_name   │
├───────────────┤
│ taro.yamada   │
│ jiro.yamada   │
│ hanako.suzuki │
└───────────────┘
                

• SQLite 3 コマンドラインツールでselectの結果を見やすくする

NULLを含む場合

区切り文字にNULLを指定した場合、他の引数の内容にかかわらず、結果はNULLになる。

sqlite> .nullvalue [null]
sqlite> select concat_ws(NULL, 'Japan', 'Tokyo', 'Shibuya');
[null]
                

区切り文字以外にNULLを指定した場合、NULLは無視され、余分な区切り文字が入ることもない。

sqlite> select concat_ws(',', NULL, 'Tokyo', 'Shibuya');
Tokyo,Shibuya
                

• SQLite 3 コマンドラインツールでnullの表示を見やすくする

参考URL

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

• SQLite3 文字列を連結する (concat(X,Y,...))
  https://sfnovicenotes.blogspot.com/2026/02/sqlite3-concatxy.html

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 SelectしたデータでUpdateする(～ver 3.29.0)

概要

select したデータを使って update するためのSQL。 select と update を別々に実行するのではなく、同時に実行できる。

2020.10.16 追記

バージョン 3.33.0 で「update～from」構文がサポートされた。 より簡単にselectしたデータでupdateすることが可能になっている。

詳しくは以下のURLより。

SQLite3 Selectした結果でUpdateする (update from)
UPDATE FROM (英語) 公式のUPDATE～FROMに関するドキュメント

以下はver 3.33.0 未満で作業したい場合のコマンド例

環境

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

insert ～ select ～ on conflict

selectした結果を使ってupsertする手順のうち、updateのみを行う。
SQLite3 でレコードがあれば更新、なければ新規挿入する (on conflict)

以下の点、注意。

• 文法の都合上「where true」が必要
• 主キーなどが重複した場合、select の結果はexcludedという一時テーブルに格納される。
  update する際はexcludedのデータを利用する。
• 主キーが重複しないレコードは新規挿入になってしまうため、 updateだけを行いたい場合はselect文で調整する。

inert into
    table_01
select
    *
from
    table_02
where true
on conflict(column01)
do update
    set
        column_02 = excluded.colum_02
        , column_03 = excluded.column_03
;
                    

update ～ select

サブクエリでselect したデータを使って update する。
ポイントは

• サブクエリのWhere句で二つのテーブルのキーを一致させる
• メインクエリのWhere句で対象のレコードを指定する

update
    table_01
set
    column_02 = (
        select
            table_02.column05
        from
            table_02
        where
            table_02.column04 = table_01.column_01
    )
where
    table_01.column_01 = XXXX
;
                    

参考URL

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: upsert (英語)
  公式のUPSERTに関するドキュメント
  https://www.sqlite.org/lang_UPSERT.html
• SQLite Query Language: UPDATE(英語)
  公式のUPDATE構文に関するドキュメント
  https://www.sqlite.org/lang_update.html
• UPDATE FROM(英語)
  公式のUPDATE FROM構文に関するドキュメント
  https://www.sqlite.org/lang_update.html#upfrom

• あさはか 備忘録: SQLite3 でレコードがあれば更新、なければ新規挿入する (on conflict)
  https://sfnovicenotes.blogspot.com/2019/07/sqlite3-on-conflict.html

SQLite3 Selectしたデータを他のテーブルに挿入(insert ～ select, replace ～ select)

概要

selectしたデータを使ってinsert , replaceするためのSQL。 selectとinsertを別々に実行するのではなく1文で済ませられる。 タイトルには「他のテーブルに」と入れたけど、同じテーブルでもOK。 (そんな機会はほぼないはず・・・)

以下のような機会に使う。

• CSVファイルを一時テーブルにインポートした後、 本番テーブルに取り込む
• マテリアライズドビューのようなものを作る
• テーブル間でデータをコピーする

insert ～ select

selectした結果をinsertする。
selectするカラム・型は挿入するテーブルのカラム・型と揃えること。

例. 「table_02」のデータを「table_01」に全てコピーする

insert into
    table_01
select
    *
from
    table_02
;
                    

例. カラムや条件を指定してselectした結果をinsertする

insert into
    table_01 (
        column_01
        , column_02
        , column_03
    )
select
    column_05
    , column_06
    , column_07
from
    table_02
where
    column_05 = XXXX
;
                    

ユニークキーが重複している場合はエラーになるので、 必要があればあらかじめチェックしておくこと。

重複していない行のみをinsertしたい場合は以下参照のこと。
SQLite 3 INSERT時にキーが重複していたら挿入しないようにする

replace ～ select

selectした結果でreplaceする。
ユニークキーが重複している場合は置換される。

例.「table02」のデータを「table01」にマージする

replace into
    table_01
select
    *
from
    table_02
;
                    

例. カラムや条件を指定してselectした結果でreplaceする

replace into
    table_01 (
        column_01
        , column_02
        , column_03
    )
select
    column_05
    , column_06
    , column_07
from
    table_02
where
    column_05 = XXXX
;
                    

replace構文については以下を参照のこと。
SQLite3 でレコードがあれば置換、なければ新規挿入する (replace)

selectしたデータでupdateをしたい場合は以下を参照のこと。
SQLite3 Select した結果でUpdateする(update from)

外部参考サイト

• SQLite 公式サイト(英語)
  https://www.sqlite.org/index.html
• SQLite Query Language: INSERT(英語)
  公式のINSERT構文に関するドキュメント
  https://www.sqlite.org/lang_insert.html
• SQLite Query Language: REPLACE(英語)
  公式のREPLACE構文に関するドキュメント
  https://www.sqlite.org/lang_replace.html