oracle中資料格式

今天遇到的一個查找資料庫百分比率的題目，在這裡先記錄下來：

select sid,to_char(sum(case when scorce>=60 then 1 else 0 end)*100/(count(*)),'FM9999')|| '%' d from test group by sid;

紅色表示計算百分比數，FM表示去除空格(因為如果它不夠4位就自動加上空格)

藍色表示加上百分号字元串“%”，用||來加。

格式化函數

函數	傳回類型	描述	例子
to_char(timestamp, text)	text	把時間戳轉換成字串	to_char(current_timestamp, 'HH12:MI:SS')
to_char(interval, text)	text	把時間間隔轉為字串	to_char(interval '15h 2m 12s', 'HH24:MI:SS')
to_char(int, text)	text	把整數轉換成字串	to_char(125, '999')
to_char(double precision, text)	text	把實數/雙精度數轉換成字串	to_char(125.8, '999D9')
to_char(numeric, text)	text	把 numeric 轉換成字串	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	把字串轉換成日期	to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)	date	把字串轉換成時間戳	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	把字串轉換成 numeric	to_number('12,454.8-', '99G999D9S')

用于日期/時間格式化的模闆

模闆	描述
HH	一天的小時數 （01-12）
HH12	一天的小時數 （01-12）
HH24	一天的小時數 （00-23）
MI	分鐘 （00-59）
SS	秒 （00-59）
MS	毫秒 (000-999)
US	微秒 (000000-999999)
SSSS	午夜後的秒 （0-86399）
AM 或 A.M. 或 PM 或 P.M.	正午辨別（大寫）
am 或 a.m. 或 pm 或 p.m.	正午辨別（小寫）
Y,YYY	帶逗号的年（4 和更多位）
YYYY	年（4和更多位）
YYY	年的後三位
YY	年的後兩位
Y	年的最後一位
BC 或 B.C. 或 AD 或 A.D.	紀元辨別（大寫）
bc 或 b.c. 或 ad 或 a.d.	紀元辨別（小寫）
MONTH	全長大寫月份名（空白填充為9字元）
Month	全長混合大小寫月份名（空白填充為9字元）
month	全長小寫月份名（空白填充為9字元）
MON	大寫縮寫月份名（3字元）
Mon	縮寫混合大小寫月份名（3字元）
mon	小寫縮寫月份名（3字元）
MM	月份号（01-12）
DAY	全長大寫日期名（空白填充為9字元）
Day	全長混合大小寫日期名（空白填充為9字元）
day	全長小寫日期名（空白填充為9字元）
DY	縮寫大寫日期名（3字元）
Dy	縮寫混合大小寫日期名（3字元）
dy	縮寫小寫日期名（3字元）
DDD	一年裡的日子（001-366）
DD	一個月裡的日子（01-31）
D	一周裡的日子（1-7；周日=1）
W	一個月裡的周數（1-5）（第一周從該月第一天開始）
WW	一年裡的周數（1-53）（第一周從該年的第一天開始）
IW	ISO 一年裡的周數（第一個星期四在第一周裡）
CC	世紀（2 位）
J	儒略日（自公元前4712年1月1日來的天數）
Q	季度
RM	羅馬數字的月份（I-XII；I=JAN）（大寫）
rm	羅馬數字的月份（I-XII；I=JAN）（小寫）
TZ	時區字串 （大寫）
tz	時區字串 （小寫）

日期/時間格式化的模闆模式修飾詞

修飾詞	描述	例子
FM 字首	填充模式(抑制填充空白和零)	FMMonth
TH 字尾	大寫順序數字尾	DDTH
th 字尾	小寫順序數字尾	DDth
FX 字首	固定格式全局選項（見用法須知）	>FX Month DD Day
SP suffix	拼寫模式（還未實作）	DDSP

FM 抑制前導的零或尾随的空白，如果沒有使用它的話，會在輸出中增加這些填充最終把輸出變成固定寬度的模式。

如果沒有使用 FX 選項， to_timestamp 和 to_date 在轉換字串的時候忽略多個空白。 FX 必須做為模闆裡的第一個項聲明。 比如 to_timestamp('2000 JUN', 'YYYY MON') 是正确的， to_timestamp('2000 JUN', 'FXYYYY MON') 會傳回一個錯誤，因為to_timestamp 隻預料會有一個空白。

在 to_char 模闆裡可以有普通文本，并且它們會被照字輸出。 你可以把一個字串放到雙引号裡強迫它解釋成一個文本， 即使它裡面包含模式關鍵字也如此。比如，在 '"Hello Year "YYYY'， YYYY 将被年份資料代替，但是Year裡單獨 的 Y 不會。

如果你想在輸出裡有雙引号，那麼你必須在它們 前面放雙反斜杠，比如 '\\"YYYY Month\\"'. （需要兩個反斜杠是因為反斜杠在字串常量裡已經有特殊含義了。）

如果你使用的年份長于 4 位字元，那麼用 YYYY 從字串向timestamp或者date 做轉換時要受到限制。 你必須在 YYYY 後面使用一些非數字字元或者模闆， 否則年份總是解釋為 4 位數字。比如（對于 2000 年）：to_date('20001121', 'YYYYMMDD') 将會被解釋成一個 4 位數字的年份，最好在年後面使用一個非數字的分隔符，象 to_date('2000-1121', 'YYYY-MMDD') 或 to_date('2000Nov31', 'YYYYMonDD')。

将字串轉化為timestamp時，毫秒（MS）和微秒（US）都是用字串 的小數點後面的部分轉換的。比如to_timestamp('12:3', 'SS:MS') 不是 3 毫秒, 而是 300，因為轉換把它看做 12 + 0.3 秒。 這意味着對于格式 'SS:MS'而言，輸入值為 12:3 或 12:30或12:300 聲明了相同數目的 毫秒。對于三毫秒，你必須使用12:003，那麼轉換會把它看做 12 + 0.003 = 12.003 秒。

用于數值格式化的模闆模式

模闆	描述
9	帶有指定數值位數的值
帶前導零的值
. (句點)	小數點
, (逗号)	分組（千）分隔符
PR	尖括号内負值
S	帶符号的數值（使用區域設定）
L	貨币符号（使用區域設定）
D	小數點（使用區域設定）
G	分組分隔符（使用區域設定）
MI	在指明的位置的負号（如果數字 <>
PL	在指明的位置的正号（如果數字 > 0）
SG	在指明的位置的正/負号
RN	羅馬數字（輸入在 1 和 3999 之間）
TH 或 th	序數字尾
V	移動指定位（小數）（參閱注解）
EEEE	科學記數。（現在還未實作）

to_char 例子

表達式	結果
to_char(current_timestamp, 'Day, DD HH12:MI:SS')	'Tuesday , 06 05:39:18'
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')	'Tuesday, 6 05:39:18'
to_char(-0.1, '99.99')	' -.10'
to_char(-0.1, 'FM9.99')	'-.1'
to_char(0.1, '0.9')	' 0.1'
to_char(12, '9990999.9')	' 0012.0'
to_char(12, 'FM9990999.9')	'0012.'
to_char(485, '999')	' 485'
to_char(-485, '999')	'-485'
to_char(485,'9 9 9')	' 4 8 5'
to_char(1485, '9,999')	' 1,485'
to_char(1485, '9G999')	' 1 485'
to_char(148.5, '999.999')	' 148.500'
to_char(148.5, 'FM999.999')	'148.5'
to_char(148.5, 'FM999.990')	'148.500'
to_char(148.5, '999D999')	' 148,500'
to_char(3148.5, '9G999D999')	' 3 148,500'
to_char(-485, '999S')	'485-'
to_char(-485, '999MI')	'485-'
to_char(485, '999MI')	'485 '
to_char(485, 'FM999MI')	'485'
to_char(485, 'PL999')	'+485'
to_char(485, 'SG999')	'+485'
to_char(-485, 'SG999')	'-485'
to_char(-485, '9SG99')	'4-85'
to_char(-485, '999PR')	'<485>'
to_char(485, 'L999')	'DM 485
to_char(485, 'RN')	' CDLXXXV'
to_char(485, 'FMRN')	'CDLXXXV'
to_char(5.2, 'FMRN')	'V'
to_char(482, '999th')	' 482nd'
to_char(485, '"Good number:"999')	'Good number: 485'
to_char(485.8, '"Pre:"999" Post:" .999')	'Pre: 485 Post: .800'
to_char(12, '99V999')	' 12000'
to_char(12.4, '99V999')	' 12400'
to_char(12.45, '99V9')	' 125'

例如：

想把一個帶小數點的Number型轉換成String型的話：

select TO_CHAR(100000.0, 'FM999,999,999,990.90') from dual;

結果是：100,000.00

說明：FM字首的作用是去除首尾的空字元或0，如果select TO_CHAR(100000.0, 'FM999,999,999,999.99') from dual;

結果是：100,000.

日期格式化：

Sql代碼 

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual;  

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"') FROM dual; 

附：oracle補零

1.前端補0：

Sql代碼  

select lpad('345',8,'0') from dual;   

select to_char('345','00000000') from dual;  

<span style="background-color: rgb(255, 255, 255);">select lpad('345',8,'0') from dual; select to_char('345','00000000') from dual;</span>

2.後端補0：

Sql代碼  

    select rpad('345',8,'0') from dual;