一、转换函数
名称 | 格式 | 功能 | 实例 | ||||||||
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. | ||||||||