SQL 関数：変換関数

TO_CHAR( 値, 書式)
TO_NUMBER( 値 )
TO_DATE( 値, 書式 )

CAST(123 AS CHAR) → ‘123’
CAST(‘123’ AS SIGNED) → 123
CAST(‘20200101’ AS DATE) → 2020-01-01
CAST(‘2020-01-01’ AS DATE) → 2020-01-01
CAST(‘2020/01/01’ AS DATE) → 2020-01-01
CAST(‘20200101’ AS DATETIME) → 2020-01-01 0:00:00

TO_CHAR( 値, 書式)
TO_DATE( 値, 書式 )
TO_TIMESTAMP( 値, 書式 )

( 値 )::TEXT
( 値 )::INT
( 値 )::JSONB

CAST( 値 AS INT)
CAST( 値 AS VARCHAR)
CAST( 値 AS DATE)

CONVERT(INTEGER , 値)
CONVERT(CHAR , 値)
CONVERT(VARCHAR , 値)
CONVERT(DATE, 値)

L：通貨記号
0：0埋めする/9：0埋めしない

SELECT TO_CHAR(‘1000’, ‘L9,999,999′) FROM MYTABLE;
— ¥1,000
SELECT TO_CHAR(‘1000’, ‘$9,999,999′) FROM MYTABLE;
— $1,000
SELECT TO_CHAR(‘1000’, ‘9,999,999’) FROM MYTABLE;
— 1,000
SELECT TO_CHAR(‘1000’, ‘L0,000,000′) FROM MYTABLE;
— ¥0,001,000
SELECT TO_CHAR(‘1000’, ‘$0,000,000′) FROM MYTABLE;
— $0,001,000
SELECT TO_CHAR(‘1000’, ‘0,000,000’) FROM MYTABLE;
— 0,001,000
SELECT TO_CHAR(‘1000’, ‘0G000G000′) FROM MYTABLE;
— 0,001,000

通貨型⇒文字列型(カンマ区切り)
10000→10,000

‘通貨型の場合
DECLARE @TestValue MONEY
SET @TestValue = 10000

CONVERT(VARCHAR, @TestValue, 1 )
⇒結果：10,000

CONVERT(VARCHAR, ‘10000’, 1)
⇒結果：10000 ‘文字列型の直接変換は不可

‘文字列型の場合は通貨型に変換してから
CONVERT(VARCHAR, CONVERT(MONEY, ‘10000’), 1)
⇒結果：10,000

SELECT TO_CHAR(0.25, ‘0,000.000’) FROM MYTABLE;
→ 0,000.250 ※整数部：0埋め 少数部：0埋め

SELECT TO_CHAR(0.25, ‘9,999.999’) FROM MYTABLE;
→　　.250 ※整数部：スペース埋め 少数部：0埋め

SELECT TO_CHAR(10.25, ‘0,000.000’) FROM MYTABLE;
→ 0,010.250 ※整数部：0埋め 少数部：0埋め

SELECT TO_CHAR(10.25, ‘9,999.999’) FROM MYTABLE;
→　　10.250 ※整数部：スペース埋め 少数部：0埋め

SELECT TO_CHAR(10.25, ‘9G999D999′) FROM MYTABLE;
→　　10.250

select
DATE_FORMAT(created_at, ‘%Y-%m-%d’)
from 〜

TO_CHARは日付型データを指定した書式の文字列に変換する。

UPDATE MYTABLE SET DATE1 = ‘2015-06-18’;
SELECT * FROM MYTABLE;

SELECT TO_CHAR(DATE1,’RR-MM-DD’) FROM MYTABLE;
→’15-06-18′

SELECT TO_CHAR(DATE1,’YY-MM-DD’) FROM MYTABLE;
→’15-06-18′

SELECT TO_CHAR(DATE1,’YY-MON-DD’) FROM MYTABLE;
→’15-6月 -18′

SELECT TO_CHAR(DATE1,’RR-MM-DD(D)’) FROM MYTABLE;
→’15-06-18(7)’
日：1 月：2 火：3 水：4 木：5 金：6 土：7

SELECT ~ ORDER BY TO_CHAR(DATE1,’D’)
曜日順(日曜先頭)
SELECT ~ ORDER BY TO_CHAR(DATE1 – 1,’D’)
曜日順(月曜先頭)

SELECT TO_CHAR(DATE1,’RR-MM-DD(DD)’) FROM MYTABLE;
→’15-06-18(土)’

SELECT TO_CHAR(DATE1,’RR-MM-DD(DAY)’) FROM MYTABLE;
→’15-06-18(土曜日)’

SELECT TO_CHAR(DATE1,’YY-MON-DD’, ‘NLS_DATE_LANGUAGE=ENGLISH‘) FROM MYTABLE;
→’15-JUN-18′

SELECT TO_CHAR(DATE1,’YY-MON-DD’, ‘NLS_DATE_LANGUAGE=JAPANESE‘) FROM MYTABLE;
→’15-6月 -18′

SELECT TO_CHAR(DATE1,’RR-MM-DD HH24:MI:SS’) FROM MYTABLE;
→’15-06-18 00:00:00′

SELECT TO_CHAR(DATE1,’YY-MM-DD HH24:MI:SS’) FROM MYTABLE;
→’15-06-18 00:00:00′

SELECT TO_CHAR(DATE1,’RR-MM-DD HH12:MI:SS’) FROM MYTABLE;
→’15-06-18 12:00:00′

SELECT TO_CHAR(DATE1,’YY-MM-DD HH12:MI:SS’) FROM MYTABLE;
→’15-06-18 12:00:00′

SELECT TO_CHAR(DATE1,’RR-MM-DD HH:MI:SS’) FROM MYTABLE;
→’15-06-18 12:00:00′

SELECT TO_CHAR(DATE1,’YY-MM-DD HH:MI:SS’) FROM MYTABLE;
→’15-06-18 12:00:00′

SELECT TO_CHAR(DATE1,’RRRR-MM-DD HH24:MI:SS’) FROM MYTABLE;
→’2015-06-18 00:00:00′

SELECT TO_CHAR(DATE1,’YYYY-MM-DD HH24:MI:SS’) FROM MYTABLE;
→’2015-06-18 00:00:00′

SELECT TO_CHAR(DATE1,’AM HH24:MI:SS’) FROM MYTABLE;
SELECT TO_CHAR(DATE1,’PM HH24:MI:SS’) FROM MYTABLE;
→’午前 00:00:00′
※AM/PMを指定すると常に正しい午前/午後が表示される’

SELECT TO_CHAR(DATE1,’YY-MON-DD HH24:MI:SS’, ‘NLS_DATE_LANGUAGE=JAPANESE’) FROM MYTABLE;
→’15-6月 -18 09:09:09′
SELECT TO_CHAR(DATE1,’fmYY-MON-DD HH24:MI:SS’, ‘NLS_DATE_LANGUAGE=JAPANESE’) FROM MYTABLE;
→’15-6月-18 9:9:9′ ※スペース、0削除
SELECT TO_CHAR(DATE1,’fmYY-MON-DD HH24:fmMI:SS’, ‘NLS_DATE_LANGUAGE=JAPANESE’) FROM MYTABLE;
→’15-6月-18 9:09:09′ ※複数回のfmで更に切替

SELECT TO_CHAR(SYSDATE,’YY-MM-DDTH‘) FROM MYTABLE;
→’15-06-08TH’ ※日付の文字表記

SELECT TO_CHAR(SYSDATE,’YY-MM-DDSP‘) FROM MYTABLE;
→’15-06-EIGHTEEN’ ※日付の文字表記

SELECT TO_CHAR(SYSDATE,’YY-MM-DDSPTH‘) FROM MYTABLE;
→’15-06-EIGHTEENTH’ ※日付の文字表記

ミリ秒の変換
SELECT TO_CHAR(SYSTIMESTAMP,’YY-MM-DD HH24:MI:SS.FF3‘) FROM MYTABLE;

WHERE句で日付型を指定する時の注意点
DATE1：’15-06-18 10:30:00’の場合、
SELECT * FROM MYTABLE WHERE DATE1 = ’15-06-18′
では抽出できない。

SELECT * FROM MYTABLE WHERE TRUNC(DATE1) = ’15-06-18′
※時間部分の切り捨て(=’15-06-18 00:00:00′)
SELECT * FROM MYTABLE WHERE TO_CHAR(DATE1,’YY-MM-DD’) = ’15-06-18′
※’YY-MM-DD’書式で文字列化(=’15-06-18′)

to_char(now(), ‘YYYY/MM/DD’)
to_char(now(), ‘YYYY/MM/DD HH24:MI:SS’)

(‘2018-01/01’)::TEXT

CAST(‘2018-01/01’ AS VARCHAR)

CONVERT(VARCHAR, 列名)

SELECT TO_CHAR(9999.99, ‘9G99B9D999′) FROM MYTABLE;
→　　9,99 9.990

SELECT TO_NUMBER(‘3’) FROM MYTABLE;
→3

(‘2018’)::INT

CAST(‘2018’ AS INT)

※非数値=0/数値=1

DECLARE @TestValue VARCHAR(2)
SET @TestValue = ‘A’

CASE ISNUMERIC( @TestValue )
WHEN 0 THEN ‘0’
WHEN 1 THEN @TestValue
END AS STAN
※数値以外の時は0に変換

SELECT TO_DATE(‘日付を表す文字列’, 日付書式) FROM MYTABLE;
SELECT TO_DATE(‘2015/01/01 10:10:10’, ‘YYYY/MM/DD HH:MI:SS’) FROM MYTABLE;
SELECT TO_DATE(’15/01/01 10:10:10′, ‘YY/MM/DD HH:MI:SS’) FROM MYTABLE;
SELECT TO_DATE(‘150101 101010’, ‘YYMMDD HHMISS’) FROM MYTABLE;

【変換時：書式を省略した場合】
SELECT TO_DATE(“02:00:00″,”HH:MI:SS”) FROM MYTABLE;
SELECT TO_CHAR(TO_DATE(“02:00:00″,”HH:MI:SS”),”YYYY/MM/DD HH24:MI:SS”) FROM MYTABLE;
– – 2015/07/01 02:00:00 ※年月日を省略した場合、年月はシステム日付が適用される。日は強制01日。

SELECT TO_DATE(“000101″,”RRMMDD”) FROM MYTABLE;
SELECT TO_CHAR(TO_DATE(“000101″,”RRMMDD”),”YYYY/MM/DD HH24:MI:SS”) FROM MYTABLE;
– – 2000/01/01 00:00:00 時刻を省略した場合、00:00:00が適用される。

【TIMESTAMP型】
TO_TIMESTAMP(‘2016-04-01 00:00:00’ , ‘YYYY-MM-DD HH24:MI:SS’)

to_date( ‘2018-01-01’ , ‘YYYY-MM-DD’ )
to_timestamp( ‘2018-01-01 10:00:00’ , ‘YYYY-MM-DD HH24:MI:SS’ )

CAST(‘2018-01-01’ AS DATE)

SELECT date_format(列名, ‘%Y/%m/%d’) as time FROM 表名;
SELECT date_format(列名, ‘%H:%i:%s’) as time FROM 表名;

DECLARE @dteFuriDay DATE;
SET @dteFuriDay = CONVERT(DATE,’20130101′)
@dteFuriDay：2013-01-01

DECLARE @chrFuriDay VARCHAR(8);
SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,111)
@chrFuriDay：2013/01/01

SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,112)
@chrFuriDay：20130101

SELECT NVL((NULL),0) FROM MYTABLE;
→0

NVL2(～1, ～2, ～3)
～1がNULLでない時⇒～2を、NULLの時⇒～3を返す。

SELECT NVL2(‘NULL’,1, 0) FROM MYTABLE;
→1
SELECT NVL2((NULL),1, 0) FROM MYTABLE;
→0

戻り値：第二引数
第二引数の型 != 第三引数の型 の場合 → 第二引数の型へ暗黙変換

NULLIF(～1, ～2)
～1 = ～2の時⇒NULLを、～1 ≠ ～2の時⇒～1を返す

COALESCE(～1, ～2, ～3)
リスト内からNULLで無い値を返す(コウアレス)
リスト：NULL, NULL, 5の時⇒5
リスト：NULL, NULL, NULLの時⇒NULL
リスト内は全て同型の必要あり

DECODE関数を用いたNULL変換も可能

NULLの変換
TO_CHAR( (NULL) )
→(NULL)
TO_NUMBER( (NULL) )
→(null)
TO_DATE( (NULL) )
→(null)

NULLの集計は「SQL グループ関数/NULLの集計」参照

ifnull(列名, 0)

NULL変換
coalesce( 列名, 0 )

空文字変換
coalesce( nullif(列名,”), ‘0’ )

SELECT ISNULL( 列名, 0 ) FROM ～
Nullの場合、０を取得

SELECT ISNULL( 列名, ” ) FROM ～
Nullの場合、”を取得