oracle学习笔记（四）--转换函数

一、转换函数

名称		格式			功能			实例
bin_to_num		bin_to_num( expr1, expr2, ... expr_n)			bit à number			bin_to_num(1,0) return 2  bin_to_num(1,1) return 3  bin_to_num(1,1,1,0) return 14
cast		cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )			请参看转换规则			select cast( '22-Aug-2003' AS varchar2(30) ) from dual; 将 日期类型22-Aug-2003转换为字符类型varchar2(30)
chartorowid		chartorowid( value1 )			Char、varchar2 、nchar、        onvarchar2 à rowid			select * from suppliers where rowid = chartorowid('AAABoqAADAAAAwPAAA');
from_tz		from_tz( timestamp_value, time_zone_value )			将 timestamp_value转换为加时区表示的方式			from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00')  return 11-Sep-05 01.50.42.000000 AM +05:00
hextoraw		hextoraw( char )			Hexadecimal à raw			hextoraw('45D') return '045D' as a raw value hextoraw('7E') return '7E' as a raw value
rawtohex		rawtohex( raw ) (在PLSQL与SQL中返回值不同)			raw àHexadecimal			rawtohex('AB')  SQL:return ‘AB’ ; PLSQL : return '4142 rawtohex('7E')  SQL:return '7E' ; PLSQL : return '3745'
numtodsinterval		numtodsinterval( number, expression ) expression的值只能是： DAY, HOUR, MINUTE, SECOND			Number à INTERVAL DAY TO SECOND			numtodsinterval(150, 'DAY') return '+000000150' numtodsinterval(1500, 'HOUR') return '+000000062' numtodsinterval(15000, 'MINUTE') return '+000000010' numtodsinterval(150000, 'SECOND') return '+000000001'
numtoyminterva		numtoyminterval( number, expression ) expression的值只能是：YEAR ,MONTH			Number à INTERVAL YEAR TO MONTH			numtoyminterval(100000000, 'MONTH') return '+0083' numtoyminterval(100000, 'YEAR') return '+0001'
to_char		to_char( value, [ format_mask ], [ nls_language ] )			Number、dateà string			to_char(1210.73, '9999.9') return '1210.7' to_char(1210.73, '$9,999.00') return '$1,210.73' to_char(sysdate, 'yyyy/mm/dd'); return '2003/07/09' to_char(sysdate, 'Month DD, YYYY'); return 'July 09, 2003'
to_clob		to_clob( expression ) expression：lob_column, char, varchar2, nchar, nvarchar2, clob, nclob			national characterà database character			select to_clob(nclob_column)from suppliers;
to_date		to_date( string1, [ format_mask ], [ nls_language ] )			Stringàdate			to_date('2003/07/09', 'yyyy/mm/dd') return July 9, 2003. to_date('070903', 'MMDDYY') return July 9, 2003. to_date('20020315', 'yyyymmdd') return  Mar 15, 2002
to_dsinterval		to_dsinterval( character [ nls_parameter ] ) character: Char,varchar2,nchar, varchar2			String à INTERVAL DAY TO SECOND			to_dsinterval('150 08:30:00') return '+000000150' to_dsinterval('80 12:30:00') return '+000000080' to_dsinterval('95 18:30:00') return '+000000095'
to_yminterval		to_yminterval( character ) character: Char,varchar2,nchar, varchar2			String à INTERVAL YEAR TO MONTH			to_yminterval('03-11')  return 3 years 11 months  to_yminterval('01-05') return 1 year 5 months to_yminterval('00-01')  return 0 years 1 month
to_lob		to_lob( long_column )			LONG , LONG RAW àOB			insert into companies (lob_column) select   to_lob(long_column) from suppliers;
to_multi_byte		to_multi_byte( char ) char:char,nchar,varchar2,nvarchar2			single-byte characters à multi-byte characters			select to_multi_byte('Tech on the net')from dual;
to_single_byte		to_single_byte( char )			multi-byte characters à single-byte characters			select to_single_byte('Tech on the net')from dual;
to_nclob		to_nclob( expression )			LOBà NCLOB			select to_nclob(lob_column)from suppliers;
to_number		to_number( string1, [ format_mask ], [ nls_language ] )			string ànumber			to_number('1210.73', '9999.99') return 1210.73 to_number('546', '999') return 546
to_timestamp		to_timestamp( string1, [ format_mask ] [ 'nlsparam' ] )			Stringàtimestamp.			to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')return '13-DEC-03 10.13.18.000000000 AM' to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')return '13-DEC-03 10.13.18.000000000 AM'
to_timestamp_tz		to_timestamp_tz( string1 , [ format_mask ] [ 'nlsparam' ] )			string à timestamp 包括时区			to_timestamp_tz('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')return '13-DEC-03 10.13.18.000000000 AM -08:00' to_timestamp_tz('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM') return '13-DEC-03 10.13.18.000000000 AM -08:00'
备注：
Cast的转换规则
TO			FROM
			char, varchar2	number		datetime , interval			raw	rowid, urowid	nchar, varchar2
char, varchar2			X	X		X			X	X
number			X	X
datetime , interval			X			X
raw			X						X
rowid, urowid			X
nchar, varchar2			X							X
日期格式
参数	说明						参数		说明
YEAR Year	spelled out						W		Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
YYYY	4-digit year						IW.		Week of year (1-52 or 1-53) based on the ISO standard
YYY、YY、Y	Last 3, 2, or 1 digit(s) of year.
IYY、IY、I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard						D		Day of week (1-7).
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1)						DAY		Name of day.
MM	Month (01-12; JAN = 01)						DD		Day of month (1-31).
MON	Abbreviated name of month.						DDD		Day of year (1-366).
MONTH	Name of month, padded with blanks to length of 9 characters.						DY		Abbreviated name of day.
RM	Roman numeral month (I-XII; JAN = I).						J		Julian day; the number of days since January 1, 4712 BC.
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.						HH		Hour of day (1-12).
MI	Minute (0-59).						HH12		Hour of day (1-12).
SS	Second (0-59).						HH24		Hour of day (0-23).
SSSSS	Seconds past midnight (0-86399).						FF		Fractional seconds.
TZM	Time zone minute.						ZH		TTime zone hour.