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蝓九a縺吶k/9シ0蝓九a縺励↑縺

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 '譁蟄怜怜梛縺ョ逶エ謗・螟画鋤縺ッ荳榊庄

'譁蟄怜怜梛縺ョ蝣エ蜷医ッ騾夊イィ蝙九↓螟画鋤縺励※縺九i
CONVERT(VARCHAR, CONVERT(MONEY, '10000'), 1)
竍堤オ先棡シ10,000

蟆乗焚轤ケ陦ィ遉コ

SELECT TO_CHAR(0.25, '0,000.000') FROM MYTABLE;
竊 0,000.250 窶サ謨エ謨ー驛ィシ0蝓九a 蟆第焚驛ィシ0蝓九a

SELECT TO_CHAR(0.25, '9,999.999') FROM MYTABLE;
竊偵縲.250 窶サ謨エ謨ー驛ィシ壹せ繝壹シ繧ケ蝓九a 蟆第焚驛ィシ0蝓九a

SELECT TO_CHAR(10.25, '0,000.000') FROM MYTABLE;
竊 0,010.250 窶サ謨エ謨ー驛ィシ0蝓九a 蟆第焚驛ィシ0蝓九a

SELECT TO_CHAR(10.25, '9,999.999') FROM MYTABLE;
竊偵縲10.250 窶サ謨エ謨ー驛ィシ壹せ繝壹シ繧ケ蝓九a 蟆第焚驛ィシ0蝓九a

SELECT TO_CHAR(10.25, '9G999D999') FROM MYTABLE;
竊偵縲10.250

濶イ縲縺ェ譌・莉倩。ィ遉コ

TO_CHAR縺ッ譌・莉伜梛繝繝シ繧ソ繧呈欠螳壹@縺滓嶌蠑上ョ譁蟄怜励↓螟画鋤縺吶k縲

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繧呈欠螳壹☆繧九→蟶ク縺ォ豁」縺励>蜊亥燕/蜊亥セ後′陦ィ遉コ縺輔l繧'

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('譌・莉倥r陦ィ縺呎枚蟄怜', 譌・莉俶嶌蠑) 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;

縲仙、画鋤譎ゑシ壽嶌蠑上r逵∫払縺励◆蝣エ蜷医
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 窶サ蟷エ譛域律繧堤怐逡・縺励◆蝣エ蜷医∝ケエ譛医ッ繧キ繧ケ繝繝譌・莉倥′驕ゥ逕ィ縺輔l繧九よ律縺ッ蠑キ蛻カ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縺碁←逕ィ縺輔l繧九

縲慎IMESTAMP蝙九
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

NULL

NULL蛟、縺ョ蛻、螳壹サ螟画鋤

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

NVL2(ス1, ス2, ス3)
ス1縺君ULL縺ァ縺ェ縺譎や抵ス2繧偵¨ULL縺ョ譎や抵ス3繧定ソ斐☆縲

SELECT NVL2('NULL',1, 0) FROM MYTABLE;
竊1
SELECT NVL2((NULL),1, 0) FROM MYTABLE;
竊0

謌サ繧雁、シ夂ャャ莠悟シ墓焚
隨ャ莠悟シ墓焚縺ョ蝙 != 隨ャ荳牙シ墓焚縺ョ蝙 縺ョ蝣エ蜷 竊 隨ャ莠悟シ墓焚縺ョ蝙九∈證鈴サ吝、画鋤

NULLIF(ス1, ス2)
ス1 = ス2縺ョ譎や誰ULL繧偵ス1 竕 ス2縺ョ譎や抵ス1繧定ソ斐☆

COALESCE(ス1, ス2, ス3)
繝ェ繧ケ繝亥縺九iNULL縺ァ辟。縺蛟、繧定ソ斐☆(繧ウ繧ヲ繧「繝ャ繧ケ)
繝ェ繧ケ繝茨シ哢ULL, NULL, 5縺ョ譎や5
繝ェ繧ケ繝茨シ哢ULL, NULL, NULL縺ョ譎や誰ULL
繝ェ繧ケ繝亥縺ッ蜈ィ縺ヲ蜷悟梛縺ョ蠢隕√≠繧

DECODE髢「謨ー繧堤畑縺縺櫻ULL螟画鋤繧ょ庄閭ス

NULL縺ョ螟画鋤
TO_CHAR( (NULL) )
竊(NULL)
TO_NUMBER( (NULL) )
竊(null)
TO_DATE( (NULL) )
竊(null)

NULL縺ョ髮險医ッ縲SQL 繧ー繝ォ繝シ繝鈴未謨ー/NULL縺ョ髮險縲榊盾辣ァ

NULL螟画鋤
coalesce( 蛻怜錐, 0 )

遨コ譁蟄怜、画鋤
coalesce( nullif(蛻怜錐,''), '0' )

SELECT ISNULL( 蛻怜錐, 0 ) FROM ス
Null縺ョ蝣エ蜷医シ舌r蜿門セ

SELECT ISNULL( 蛻怜錐, '' ) FROM ス
Null縺ョ蝣エ蜷医''繧貞叙蠕

Follow me!