Oracle 备忘 substr least decode to_char to_date

冯869

Applies To:
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g
For example:
substr('This is a test', 6, 2)would return 'is'
substr('This is a test', 6)would return 'is a test'
substr('TechOnTheNet', 1, 4)would return 'Tech'
substr('TechOnTheNet', -3, 3)would return 'Net'
substr('TechOnTheNet', -6, 3)would return 'The'
substr('TechOnTheNet', -8, 2)would return 'On'
For example:
least(2, 5, 12, 3)would return 2
least('2', '5', '12', '3')would return '12'
least('apples', 'oranges', 'bananas')would return 'apples'
least('apples', 'applis', 'applas')would return 'applas'
least('apples', 'applis', 'applas', null)would return NULL
decode( expression , search , result [, search , result]... [, default] )
For example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name,
decode(supplier_id,10000,'IBM',
10001,'Microsoft',
10002,'Hewlett Packard',
'Gateway') result
FROM suppliers;
For example:
to_char(1210.73, '9999.9')would return '1210.7'
to_char(1210.73, '9,999.99')would return '1,210.73'
to_char(1210.73, '$9,999.00')would return '$1,210.73'
to_char(21, '000099')would return '000021'
to_char(sysdate, 'yyyy/mm/dd');would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY');would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');would return 'Jul 9th, 2003'
For example:
to_date('2003/07/09', 'yyyy/mm/dd')would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY')would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd')would return a date value of Mar 15, 2002.
to_char(to_date(g.exam_date, 'YYYYMMDD'), 'day') return 星期X