SQL 関数:変換関数

型変換

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

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

色々な日付表示

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)

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)

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

NULL

NULL値の判定・変換

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の集計」参照

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

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