Mấy cái hàm Ora thông dụng

TO_CHAR

Convert a numeric or date expression to a character String.

Syntax
      to_char(expression [,'format'] [,nls_format])

Key
   char      The DATE, NUMBER or expression to convert
   format    Format to use.
   nls_lang  The international language to use.

The format may be either a DATE format (YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute )
or a NUMBER format (0999=include leading zero).

If no format is specified Oracle will use the default date format.
nls_format allows international formats to be applied.
TO_CHAR will convert NCHAR, NVARCHAR2, CLOB, or NCLOB data to the database character set.
Examples

SQL> Select to_char(sysdate, 'yyyy/mm/dd') FROM dual;
 '2010/12/24'

SQL> Select to_char(sysdate, 'FMMonth DD, YYYY') FROM dual;
 'June 9, 2005'

SQL> select to_char(sysdate,'HH24:MI:SS') "Time Now" from dual;
 '14:35:56'

SQL> Select to_char(1.234, '9999.9') FROM dual;
 '1.2'

SQL> Select to_char(1000.25, '9,999.99') FROM dual;
 '1,000.25'

SQL> Select to_char(1000.25, '$9,999.00') FROM dual;
 '$1,000.25'

SQL> Select to_char(25, '000099') FROM dual;
 '000025'

SQL> Select to_char(-50, 'PR999') FROM dual;
 '<50>'

SQL> Select to_char(17, 'RN99') FROM dual;
 'XVII'

SQL> Select to_char('01110' + 1) FROM dual;
 1111

SQL> Select to_char(timestamp, 'DD-MM-YYYY HH24:MI') FROM dual;
 31-12-2005 23.30

Convert a character string into an Oracle date, then convert back to a string with a different date format:

SQL> Select to_char(mydate,'DD-MON-RR HH12:MI') Short_Date_Time
 from (
   select to_date('1-MAR-2010 23:24','DD-MON-RRRR HH24:MI') mydate
   from dual
 );

TO_DATE

Convert an expression to a date value.
Syntax to_date(char[,'format'[,nls_lang]) Key char String expression that will be converted to a date format Date format to use. nls_lang The international language to use. to_date will convert either a character string or an expression into a date value.

The 'format' must be a valid DATE format: YYYY=year, MM=month, DD=Day, HH=Hour, Mi=Minute
If no format is specified Oracle will assume the default date format has been supplied in char.
Examples
to_date('29-Oct-09', 'DD-Mon-YY') to_date('10/29/09', 'MM/DD/YY') to_date('120109', 'MMDDYY') to_date('29-Oct-09', 'DD-Mon-YY HH:MI:SS') to_date('Oct/29/09', 'Mon/DD/YY HH:MI:SS') to_date('October.29.2009', 'Month.DD.YYYY HH:MI:SS') SQL> select * from sales where order_date > to_date('29-Oct-09', 'DD-Mon-YY'); To check that year 2000 dates are appearing correctly try the following:
SELECT to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),1 * 12),'DD-MON-YYYY') y1999, to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),2 * 12),'DD-MON-YYYY') y2000, to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),7 * 12),'DD-MON-YYYY') y2005, to_char(add_months(to_date('01-JAN-1998', 'DD-MON-YYYY'),52 * 12),'DD-MON-YYYY') y2050 FROM DUAL; -- Expected output -- Y1999 Y2000 Y2005 Y2050 -- ----------- ----------- ----------- ----------- -- 01-JAN-1999 01-JAN-2000 01-JAN-2005 01-JAN-2050

TO_NUMBER

Convert a string expression to a number
Syntax to_number(char[,'format'[,nls_lang]) Key char String expression that will be converted to a number format Date format to use. nls_lang The international language to use. You can convert a character or expression that contains a number into an actual number value.

The 'format' must be a valid Number format.

nls_lang allows international formats to be applied e.g. currency symbols and numeric chars.
ORA-01722: invalid number - error thrown if TO_NUMBER is passed a string that doesn't represent a number. See René Nyffenegger's safe_to_number function.
Examples

SQL> Select to_number('1234.64') from Dual;
1234.64

SQL> Select to_number('1234.64', '9999.9') from Dual;
1234.6

SQL> Select to_number('$99.64', 'L99D99') from Dual;
99.64

CASE and Width_Bucket Functions

Case Function
A flexible method of grouping data into even or unevenly sized buckets.
Very similar to DECODE
Syntax:

CASE WHEN <cond1> THEN <Value1>
     WHEN <cond2> THEN <Value2>
     [ELSE Value n ] END   

A single CASE statement can be selected (along with other columns), resulting in a vertical list of data buckets. Alternatively several case statements can be summed to display totals in a horizontal row:

 SELECT CASE WHEN sal>1000 THEN 'Over paid'
             ELSE 'Under paid' END
 FROM   emp;


 SELECT SUM(CASE WHEN SUM(amount) BETWEEN 0 AND 49 THEN 1 
            ELSE 0 END) AS "0-49",
        SUM(CASE WHEN SUM(amount) BETWEEN 50 AND 100
           THEN 1 ELSE 0 END) AS "50-100"
 FROM sales;
 

WIDTH_BUCKET Function
Divide a data set into buckets with an equal interval size.
e.g. Age = 0-20, 20-40, 40-60, 60-80…
This is known as an 'equiwidth histogram'.
Syntax:

WIDTH_BUCKET(column/expr, low boundary, high_boundary,
              bucket_count)

If you ask for (n) buckets you actually get (n+2) buckets
The extra 2 being for values above and below the high/low boundaries.

e.g.
SELECT last_name, salary,
       WIDTH_BUCKET(salary,3000,9000,3)

Will create 5 buckets:
Up_to_3000, 3000-5000, 5000-7000, 7000-9000, 9000+

When using WIDTH_BUCKET pay attention to the boundary values, each bucket will contain values equal to or greater than the lowest boundary of that bucket, so age ranges 0-20, 20-40… would actually be 0-19.99 and 20-39.999…

"Our team is well balanced. We have problems everywhere." - Tommy Prothro