Oracle PL/SQL Tutorial/Conversion Functions/TO CHAR — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 Format Parameters
- 2 TO_CHAR(0012345.6700, "FM99999.99")
- 3 TO_CHAR(0.67, "B9.99")
- 4 TO_CHAR(12345.67, "099,999.99") (4)
- 5 TO_CHAR(12345.67, "$99,999.99")
- 6 TO_CHAR(12345.67, "99,999.9900")
- 7 TO_CHAR(12345.67, "99,999.99") (1)
- 8 TO_CHAR(12345.67, "99999.99") (2)
- 9 TO_CHAR(-12345.67, "99,999.99") (3)
- 10 TO_CHAR(12345.67, "99999.99EEEE")
- 11 TO_CHAR(-12345.67, "99,999.99MI")
- 12 TO_CHAR(-12345.67, "99,999.99PR")
- 13 TO_CHAR(12345.67, "99999D99")
- 14 TO_CHAR(12345.67, "99999G99")
- 15 TO_CHAR(12345.67, "99999V99")
- 16 TO_CHAR(12345.67, "C99,999.99")
- 17 TO_CHAR(12345.67, "L99,999.99")
- 18 TO_CHAR(12345.67, "TM")
- 19 TO_CHAR(12345.67, "U99,999.99")
- 20 TO_CHAR(2007, "RN")
- 21 to_char(bdate,"fmMonth ddth, yyyy")
- 22 TO_CHAR(last_ddl_time,"dd-mon-yyyy hh24:mi")
- 23 to_char(sysdate,"DAY dy Dy")
- 24 to_char(sysdate, "Day", "nls_date_language=Dutch")
- 25 to_char(sysdate,"hh24:mi:ss") as time
- 26 to_char(sysdate,"MONTH mon") as month
- 27 to_char(to_date("01/01/2006","dd/mm/yyyy"),is on Day") as new_year_2006
- 28 TO_CHAR() will return a string of pound characters (#) if you try and format a number that contains too many digits for the format you have provided.
- 29 TO_CHAR(x [, format]) converts x to a string.
- 30 use to_char more than once to create long date format
- 31 Use TO_CHAR() to convert columns containing numbers to strings.
Format Parameters
TO_CHAR(number, format, NLS_Params)
The format mask and the NLS parameters are identical to the TO_NUMBER function.
The NLS parameters again are
- NLS_NUMERIC_CHARACTERS -- Specifies characters to use for group separators and the decimal point.
- NLS_CURRENCY -- Specifies the local currency.
- NLS_ISO_CURRENCY -- Character(s) to represent the ISO currency symbol.
The optional format string you may pass to TO_CHAR() has a number of parameters that affect the string returned by TO_CHAR().
Some of these parameters are listed in the following table.
Parameter Format Examples Description 9 999 Returns digits in specified positions with leading negative sign if the number is negative. 0 0999 9990 0999: Returns a number with leading zeros.9990: Returns a number with trailing zeros. . 999.99 Returns a decimal point in the specified position. , 9,999 Returns a comma in the specified position. $ $999 Returns a leading dollar sign. B B9.99 If the integer part of a fixed point number is zero, returns spaces for the zeros. C C999 Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter. D 9D99 Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character). EEEE 9.99EEEE Returns number using the scientific notation. FM FM90.9 Removes leading and trailing spaces from number. G 9G999 Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter. L L999 Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter. MI 999MI Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space. PR 999PR Returns a negative number in angle brackets (< >). Returns a positive number with leading and trailing spaces. RN rn RN rn Returns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999. S S999 999S S999: Returns a negative number with a leading negative sign; returns a positive number with a leading positive sign.999S: Returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign. TM TM Returns a number using the minimum number of characters. Default is TM9, which returns the number using fixed notation unless the number of characters is greater than 64. If greater than 64, the number is returned using scientific notation. U U999 Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter. V 99V99 Returns number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded. X XXXX Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
TO_CHAR(0012345.6700, "FM99999.99")
SQL> Select TO_CHAR(0012345.6700, "FM99999.99") from dual;
TO_CHAR(0
---------
12345.67
TO_CHAR(0.67, "B9.99")
SQL> Select TO_CHAR(0.67, "B9.99") from dual;
TO_CH
-----
.67
TO_CHAR(12345.67, "099,999.99") (4)
SQL> Select TO_CHAR(12345.67, "099,999.99") from dual;
TO_CHAR(123
-----------
012,345.67
SQL>
TO_CHAR(12345.67, "$99,999.99")
SQL> Select TO_CHAR(12345.67, "$99,999.99") from dual;
TO_CHAR(123
-----------
$12,345.67
TO_CHAR(12345.67, "99,999.9900")
SQL> Select TO_CHAR(12345.67, "99,999.9900") from dual;
TO_CHAR(1234
------------
12,345.6700
TO_CHAR(12345.67, "99,999.99") (1)
TO_CHAR() converts 12345.9067 to a string and specifies this number is to be converted using the format 99,999.99.
This results in the string returned by TO_CHAR() having a comma to delimit the thousands:
SQL> SELECT TO_CHAR(12345.67, "99,999.99") FROM dual;
TO_CHAR(12
----------
12,345.67
SQL>
TO_CHAR(12345.67, "99999.99") (2)
SQL>
SQL> Select TO_CHAR(12345.67, "99999.99") from dual;
TO_CHAR(1
---------
12345.67
TO_CHAR(-12345.67, "99,999.99") (3)
SQL> Select TO_CHAR(-12345.67, "99,999.99") from dual;
TO_CHAR(-1
----------
-12,345.67
SQL>
TO_CHAR(12345.67, "99999.99EEEE")
SQL> Select TO_CHAR(12345.67, "99999.99EEEE") from dual;
TO_CHAR(12
----------
1.23E+04
TO_CHAR(-12345.67, "99,999.99MI")
SQL> Select TO_CHAR(-12345.67, "99,999.99MI") from dual;
TO_CHAR(-1
----------
12,345.67-
TO_CHAR(-12345.67, "99,999.99PR")
SQL> Select TO_CHAR(-12345.67, "99,999.99PR") from dual;
TO_CHAR(-12
-----------
<12,345.67>
TO_CHAR(12345.67, "99999D99")
SQL> Select TO_CHAR(12345.67, "99999D99") from dual;
TO_CHAR(1
---------
12345.67
TO_CHAR(12345.67, "99999G99")
SQL> Select TO_CHAR(12345.67, "99999G99") from dual;
TO_CHAR(1
---------
123,46
TO_CHAR(12345.67, "99999V99")
SQL> Select TO_CHAR(12345.67, "99999V99") from dual;
TO_CHAR(
--------
1234567
TO_CHAR(12345.67, "C99,999.99")
SQL> Select TO_CHAR(12345.67, "C99,999.99") from dual;
TO_CHAR(12345.67,
-----------------
USD12,345.67
TO_CHAR(12345.67, "L99,999.99")
SQL> Select TO_CHAR(12345.67, "L99,999.99") from dual;
TO_CHAR(12345.67,"L9
--------------------
$12,345.67
TO_CHAR(12345.67, "TM")
SQL> Select TO_CHAR(12345.67, "TM") from dual;
TO_CHAR(12345.67,"TM")
----------------------------------------------------------------
12345.67
TO_CHAR(12345.67, "U99,999.99")
SQL> Select TO_CHAR(12345.67, "U99,999.99") from dual;
TO_CHAR(12345.67,"U9
--------------------
$12,345.67
TO_CHAR(2007, "RN")
SQL> Select TO_CHAR(2007, "RN") from dual;
TO_CHAR(2007,"R
---------------
MMVII
to_char(bdate,"fmMonth ddth, yyyy")
SQL> create table employees(
2 empno NUMBER(4)
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , msal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) ) ;
Table created.
SQL>
SQL>
SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10);
1 row created.
SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10);
1 row created.
SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20);
1 row created.
SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20);
1 row created.
SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30);
1 row created.
SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30);
1 row created.
SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40);
1 row created.
SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40);
1 row created.
SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20);
1 row created.
SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30);
1 row created.
SQL>
SQL>
SQL> select ename
2 , to_char(bdate,"fmMonth ddth, yyyy")
3 from employees;
ENAME TO_CHAR(BDATE,"FMMON
-------- --------------------
Jason December 18th, 1965
Jerry November 19th, 1966
Jord October 21st, 1967
Mary September 22nd, 1968
Joe August 23rd, 1969
Black July 24th, 1970
Red June 25th, 1971
White May 26th, 1972
Yellow April 27th, 1973
Pink March 28th, 1974
10 rows selected.
SQL>
SQL> drop table employees;
Table dropped.
TO_CHAR(last_ddl_time,"dd-mon-yyyy hh24:mi")
SQL>
SQL> SELECT owner, object_name, object_type, TO_CHAR(last_ddl_time,"dd-mon-yyyy hh24:mi") last_ddl_time
2 FROM dba_objects
3 where rownum<50;
OWNER
------------------------------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECT_TYPE LAST_DDL_TIME
------------------- -----------------
SYS
ICOL$
TABLE 07-feb-2006 22:38
SYS
I_USER1
INDEX 07-feb-2006 22:10
SYS
CON$
TABLE 07-feb-2006 22:38
SYS
UNDO$
TABLE 07-feb-2006 22:10
SYS
C_COBJ#
CLUSTER 07-feb-2006 22:10
SYS
I_OBJ#
INDEX 07-feb-2006 22:10
SYS
PROXY_ROLE_DATA$
TABLE 07-feb-2006 22:10
SYS
I_IND1
INDEX 07-feb-2006 22:10
SYS
I_CDEF2
INDEX 07-feb-2006 22:10
SYS
I_PROXY_ROLE_DATA$_1
INDEX 07-feb-2006 22:10
SYS
FILE$
TABLE 07-feb-2006 22:10
SYS
UET$
TABLE 07-feb-2006 22:10
SYS
I_FILE#_BLOCK#
INDEX 07-feb-2006 22:10
SYS
I_FILE1
INDEX 07-feb-2006 22:10
SYS
I_CON1
INDEX 07-feb-2006 22:10
SYS
I_OBJ3
INDEX 07-feb-2006 22:10
SYS
I_TS#
INDEX 07-feb-2006 22:10
SYS
I_CDEF4
INDEX 07-feb-2006 22:10
SYS
IND$
TABLE 07-feb-2006 22:38
SYS
SEG$
TABLE 07-feb-2006 22:10
SYS
C_TS#
CLUSTER 07-feb-2006 22:10
SYS
I_FILE2
INDEX 07-feb-2006 22:10
SYS
COL$
TABLE 07-feb-2006 22:38
SYS
I_TS1
INDEX 07-feb-2006 22:10
SYS
I_UNDO2
INDEX 07-feb-2006 22:10
SYS
CLU$
TABLE 07-feb-2006 22:10
SYS
PROXY_DATA$
TABLE 07-feb-2006 22:10
SYS
I_PROXY_DATA$
INDEX 07-feb-2006 22:10
SYS
I_OBJ1
INDEX 07-feb-2006 22:10
SYS
I_COL2
INDEX 07-feb-2006 22:10
SYS
I_OBJ2
INDEX 07-feb-2006 22:10
SYS
I_CCOL1
INDEX 07-feb-2006 22:10
SYS
TS$
TABLE 07-feb-2006 22:38
SYS
C_FILE#_BLOCK#
CLUSTER 07-feb-2006 22:10
SYS
C_USER#
CLUSTER 07-feb-2006 22:10
SYS
I_UNDO1
INDEX 07-feb-2006 22:10
SYS
BOOTSTRAP$
TABLE 07-feb-2006 22:10
SYS
FET$
TABLE 07-feb-2006 22:10
SYS
I_TAB1
INDEX 07-feb-2006 22:10
SYS
CCOL$
TABLE 07-feb-2006 22:38
SYS
USER$
TABLE 07-feb-2006 22:52
SYS
I_CON2
INDEX 07-feb-2006 22:10
SYS
I_COBJ#
INDEX 07-feb-2006 22:10
SYS
OBJ$
TABLE 07-feb-2006 22:52
SYS
I_COL3
INDEX 07-feb-2006 22:10
SYS
C_OBJ#
CLUSTER 07-feb-2006 22:10
SYS
TAB$
TABLE 07-feb-2006 22:38
SYS
CDEF$
TABLE 07-feb-2006 22:38
SYS
OWNER
------------------------------
OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECT_TYPE LAST_DDL_TIME
------------------- -----------------
I_CDEF1
INDEX 07-feb-2006 22:10
49 rows selected.
SQL>
to_char(sysdate,"DAY dy Dy")
SQL>
SQL>
SQL> select to_char(sysdate,"DAY dy Dy") as day
2 from dual;
DAY
-----------------
THURSDAY thu Thu
SQL>
to_char(sysdate, "Day", "nls_date_language=Dutch")
SQL>
SQL>
SQL> select to_char(sysdate, "Day")
2 , to_char(sysdate, "Day", "nls_date_language=Dutch")
3 from dual;
TO_CHAR(S TO_CHAR(S
--------- ---------
Thursday Donderdag
SQL>
to_char(sysdate,"hh24:mi:ss") as time
SQL>
SQL>
SQL> select sysdate as today
2 , to_char(sysdate,"hh24:mi:ss") as time
3 from dual;
TODAY TIME
--------- --------
24-JUL-08 18:56:03
SQL>
to_char(sysdate,"MONTH mon") as month
SQL>
SQL>
SQL> select to_char(sysdate,"MONTH mon") as month
2 from dual;
MONTH
-------------
JULY jul
SQL>
to_char(to_date("01/01/2006","dd/mm/yyyy"),is on Day") as new_year_2006
SQL>
SQL>
SQL> select sysdate as today
2 , to_char(to_date("01/01/2006","dd/mm/yyyy"),""is on "Day") as new_year_2006
3 from dual;
TODAY NEW_YEAR_2006
--------- ---------------
24-JUL-08 is on Sunday
SQL>
SQL>
TO_CHAR() will return a string of pound characters (#) if you try and format a number that contains too many digits for the format you have provided.
SQL>
SQL> SELECT TO_CHAR(12345678.90, "99,999.99") FROM dual;
TO_CHAR(12
----------
##########
SQL>
Pound characters are returned by TO_CHAR() because the number 12345678.90 has more digits than those allowed in the format string 99,999.99.
TO_CHAR(x [, format]) converts x to a string.
You can provide an optional format that indicates the format of x.
The structure format depends on whether x is a number or date.
The following example converts 12345.67 to a string:
SQL> SELECT TO_CHAR(12345.67) FROM dual;
TO_CHAR(
--------
12345.67
SQL>
use to_char more than once to create long date format
SQL>
SQL>
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values
2 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");
1 row created.
SQL>
SQL>
SQL> select gift_id, "Order placed on the " ||
2 to_char(register_date, "fmddth") || " day of "||
3 to_char(register_date, "fmMonth") || ", " ||
4 to_char(register_date, "yyyy") as "Order date"
5 from gift;
GIFT_ID Order date
---------- -----------------------------------------------
1 Order placed on the 14th day of February, 1999
2 Order placed on the 14th day of February, 1999
3 Order placed on the 14th day of February, 1999
4 Order placed on the 14th day of February, 1999
5 Order placed on the 4th day of March, 1999
5 rows selected.
SQL>
SQL>
SQL>
SQL> drop table gift;
Table dropped.
Use TO_CHAR() to convert columns containing numbers to strings.
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SELECT id, "The salary of this employee is" || TO_CHAR(salary, "$99999999.99")
2 FROM employee;
ID "THESALARYOFTHISEMPLOYEEIS"||TO_CHAR(SALARY
---- -------------------------------------------
01 The salary of this employee is $1234.56
02 The salary of this employee is $6661.78
03 The salary of this employee is $6544.78
04 The salary of this employee is $2344.78
05 The salary of this employee is $2334.78
06 The salary of this employee is $4322.78
07 The salary of this employee is $7897.78
08 The salary of this employee is $1232.78
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>