SQL 使いやすい関数とCASE式

文字列連結

# || で文字列連結
select name, category, name || category as name_category from soft;

日付系

# 現在の日付を取得
select current_date;

# 現在の時間
select current_time;

# 現在の日時
select current_timestamp;

# extract
# 日付要素の切り出し 戻り値数値なので注意
select current_timestamp as now, 
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;

COALESCE (コアレス)

左から順に引数を見て、最初にNULLでない値を返す。

select coalesce (null, 1) as col_1, coalesce (null, 'abc', null) as col_2, coalesce (null, null, '12345678') as col_3;

LIKE (あいまい検索)

# 前方一致
select * from soft where name like '%a';


# 中間一致
select * from soft where name like '%a%';


# 後方一致
select * from soft where name like 'a%';

BETWEEN (範囲検索)

select * from soft where price between 200 and 1000;

# 以下と同じ
select * from soft where price >= 200 and price <= 1000;

IN (ORの省略形)

select * from soft where price in (200, 400, 800);

# 以下と同じ
select * from soft where price = 200 or  where price = 400,  where price = 800;

# NOTにする
select * from soft where price not in (200, 400, 800);

CASE式

条件分岐をする

# 構文

CASE 
WHEN <評価式> THEN <式> 
WHEN <評価式> THEN <式>
WHEN <評価式> THEN <式> 
WHEN <評価式> THEN <式> 
ELSE <式>
END

使う

select * , 
case 
when category = 'Editor'
then 'Eから始まる' || category || 'ですよ'
when category = 'IDE'
then 'Iから始まる' || category || 'ですよ'
else null
end as if_category
from soft;

※endを忘れないように。