SQLのコア関数

コア関数

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【文字列の文字数を取得(length関数)】
書式
length(文字列)

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> create table product(id integer, namee text, namej text);
sqlite> insert into product values(1, 'Apple', 'りんご');
sqlite> insert into product values(2, 'Car', '車');
sqlite> insert into product values(3, 'Television', 'テレビ');
sqlite> insert into product values(4, 'Mobile', '携帯');
sqlite> select namee, length(namee), namej, length(namej) from product;
namee length(namee) namej length(namej)
---------- ------------- ---------- -------------
Apple 5 りんご 3
Car 3 車 1
Television 10 テレビ 3
Mobile 6 携帯 2
sqlite>

【文字列をすべて大文字または小文字に変換(lower関数, upper関数)】
書式：文字→小
lower(文字列)
または
書式：文字→小
upper(文字列)

【SQL文】
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table fruit(id integer, name text);
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> insert into fruit values(1, 'Apple');
sqlite> insert into fruit values(2, 'KiwiFruit');
sqlite> insert into fruit values(3, 'Peach');
sqlite> insert into fruit values(4, 'Strawberry');
sqlite> select * from fruit;
id name
---------- ----------
1 Apple
2 KiwiFruit
3 Peach
4 Strawberry
sqlite> select name, lower(name), upper(name) from fruit;
name lower(name) upper(name)
---------- ----------- -----------
Apple apple APPLE
KiwiFruit kiwifruit KIWIFRUIT
Peach peach PEACH
Strawberry strawberry STRAWBERRY
sqlite>

【文字列を別の文字列に置換(replace関数)】
書式
replace(対象の文字列, 置換される文字列, 置換する文字列)

replace 関数を使って name カラム に格納されている値のなかの 2018 をすべて 2019 に変更した値を取得
SQL文
sqlite> create table report(id integer, name text);
sqlite> insert into report values(1, 'report_2018_Tokyo');
sqlite> insert into report values(2, 'report_2018_Kyoto');
sqlite> insert into report values(3, 'report_2018_Nagoya');
sqlite> insert into report values(4, 'report_2017_Tokyo');
sqlite> .header on
sqlite> .mode column
sqlite> select * from report;
id name
---------- -----------------
1 report_2018_Tokyo
2 report_2018_Kyoto
3 report_2018_Nagoy
4 report_2017_Tokyo
sqlite> select name, replace(name, '2018', '2019') from report;
name replace(name, '2018', '2019')
----------------- -----------------------------
report_2018_Tokyo report_2019_Tokyo
report_2018_Kyoto report_2019_Kyoto
report_2018_Nagoy report_2019_Nagoya
report_2017_Tokyo report_2017_Tokyo
sqlite>

【substr関数の使い方】
書式
substr(文字列, 開始位置, 文字の長さ)
substr(文字列, 開始位置)
SQL文
sqlite> create table staff(id integer, name text, address text);
sqlite> insert into staff values(1, 'Yamada', '01:Tokyo');
sqlite> insert into staff values(2, 'Suzuki', '02:Osaka');
sqlite> insert into staff values(3, 'Hori', '03:Sapporo');
sqlite> insert into staff values(4, 'Kudou', '01:Tokyo');
sqlite> insert into staff values(5, 'Oota', '04:Nagoya');

substr 関数を使って address カラム に格納されている値の中から4番目の文字から3文字分だけ部分文字列を取得
sqlite> select id, name, substr(address, 4, 3) from staff;
id name substr(address, 4, 3)
---------- ---------- ---------------------
1 Yamada Tok
2 Suzuki Osa
3 Hori Sap
4 Kudou Tok
5 Oota Nag
sqlite>

【文字列の先頭と最後から空白文字を取り除く(trim関数, rtrim関数, ltrim関数)】
trim関数→指定した文字列の先頭や最後に付いている空白文字を取り除く
書式
trim(文字列)
trim(文字列, 文字)

rtrim 関数→文字列の最後から指定した文字を削除する
書式
rtrim(文字列)
rtrim(文字列, 文字)

ltrim 関数→文字列の先頭から指定した文字を削除する
書式
ltrim(文字列)
ltrim(文字列, 文字)

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table msgdb(id integer, msg text);
sqlite> insert into msgdb values(1, ' Hello! ');
sqlite> insert into msgdb values(2, ' Good Bye ');
sqlite> insert into msgdb values(3, '__Thank you__');
sqlite> .header on
sqlite> .mode column
sqlite> select id, quote(trim(msg)), quote(ltrim(msg)), quote(rtrim(msg)) from msgdb;
id quote(trim(msg)) quote(ltrim(msg)) quote(rtrim(msg))
---------- ---------------- ----------------- -----------------
1 'Hello!' 'Hello! ' ' Hello!'
2 'Good Bye' 'Good Bye ' ' Good Bye'
3 '__Thank you__' '__Thank you__' '__Thank you__'
sqlite>
msg カラムに格納されている値の中から先頭と最後にあるアンダーバー(_)を取り除いた文字列を取得してみる。
sqlite> select id, trim(msg, '_'), ltrim(msg, '_'), rtrim(msg, '_') from msgdb;
id trim(msg, '_') ltrim(msg, '_') rtrim(msg, '_')
---------- -------------- --------------- ---------------
1 Hello! Hello! Hello!
2 Good Bye Good Bye Good Bye
3 Thank you Thank you__ __Thank you
sqlite>
【文字列をシングルクォーテーションで囲った値を取得(quote関数)】
書式
quote(値)

SQL文
sqlite> create table datadb(id, data);
sqlite>
sqlite> insert into datadb values(1, 'Melon'); insert into datadb values(2, 18); insert into datadb values(3, 3.52); insert into datadb values(4, 'I''am a student');
sqlite> select * from datadb;
id data
---------- ----------
1 Melon
2 18
3 3.52
4 I'am a stu
sqlite> select id, data, quote(data) from datadb;
id data quote(data)
---------- ---------- -----------
1 Melon 'Melon'
2 18 18
3 3.52 3.52
4 I'am a stu 'I''am a st
sqlite>
カラムに格納されている値に対して数値はそのままで文字列についてはシングルクォーテーションで囲った値を取得する

【数値を四捨五入した値を取得する(round関数)】
書式
round(数値)
round(数値, 桁)

SQL文
sqlite> create table point(id integer, point real);
sqlite> insert into point values(1, 15.4853);
sqlite> insert into point values(2, 27.143);
sqlite> insert into point values(3, 38.902);
sqlite> insert into point values(4, 26.5521);
sqlite> insert into point values(5, 30.36);
sqlite> select * from point;
id point
---------- ----------
1 15.4853
2 27.143
3 38.902
4 26.5521
5 30.36
sqlite> select id, point, round(point) from point;
id point round(point)
---------- ---------- ------------
1 15.4853 15.0
2 27.143 27.0
3 38.902 39.0
4 26.5521 27.0
5 30.36 30.0
sqlite>
sqlite> select id, point, round(point, 1), round(point, 2) from point;
id point round(point, 1) round(point, 2)
---------- ---------- --------------- ---------------
1 15.4853 15.5 15.49
2 27.143 27.1 27.14
3 38.902 38.9 38.9
4 26.5521 26.6 26.55
5 30.36 30.4 30.36
sqlite>
【数値の絶対値を取得する(abs関数)】
書式
abs(数値)
例文
abs(0.47); /* 0.47 */
abs(-19); /* 19 */
abs(NULL); /* NULL */
abs('-18.5'); /* 18.5 */
abs('pen'); /* 0.0 */

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> create table test(id, data);
sqlite>
sqlite> insert into test values(1, 18);
sqlite> insert into test values(2, -7.4);
sqlite> insert into test values(3, NULL);
sqlite> insert into test values(4, 'Flower');
sqlite> insert into test values(5, '-16');
sqlite>
sqlite> select id, data, abs(data) from test;
id data abs(data)
---------- ---------- ----------
1 18 18
2 -7.4 7.4
3
4 Flower 0.0
5 -16 16.0
sqlite>

【整数の乱数を生成(random関数)】
書式
random()

SQL文
sqlite> select random() % 10, abs(random()) % 10;
random() % 10 abs(random()) % 10
------------- ------------------
9 1
sqlite>