Oracle PL/SQL/Conversion Functions/TO CHAR
Содержание
- 1 CONVERSION functions: Print the current date/time as a character string and modifies the format
- 2 convert a negative number into one with a trailing "minus sign,"
- 3 Pass variable into to_char function
- 4 The TO_CHAR() function converts the expression into character data.
- 5 to_char(bdate,"fmMonth ddth, yyyy")
- 6 to_char(round(sqrt(sal),2),"9999.99")
- 7 to_char(sysdate,"DAY dy Dy") as day, to_char(sysdate,"MONTH mon") as month
- 8 to_char(sysdate, "Day", "nls_date_language=Dutch")
- 9 to_char(sysdate,"hh24:mi:ss") as time
- 10 Use to_char function to init an text value
CONVERSION functions: Print the current date/time as a character string and modifies the format
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_sysdate DATE := SYSDATE;
3 v_date DATE;
4 v_char VARCHAR2(20);
5 BEGIN
6
7
8
9 v_char := TO_CHAR(v_sysdate, "DD:MM:YYYY HH24:MI:SS");
10 DBMS_OUTPUT.PUT_LINE("Display as CHARACTER DD:MM:YYYY HH24:MI:SS: "||v_char);
11
12
13 END;
14 /
Display as CHARACTER DD:MM:YYYY HH24:MI:SS: 26:10:2009 10:41:36
PL/SQL procedure successfully completed.
SQL>
convert a negative number into one with a trailing "minus sign,"
SQL>
SQL> SELECT
2 TO_CHAR(-1234,"9999MI") result
3 FROM dual;
RESUL
-----
1234-
SQL>
Pass variable into to_char function
SQL> DECLARE
2 lv_format_txt CONSTANT VARCHAR2(11) := "$99,999.99";
3 lv_test_txt VARCHAR2(11);
4 lv_test_num NUMBER := 54321;
5 BEGIN
6 lv_test_txt := TO_CHAR(lv_test_num, lv_format_txt);
7 DBMS_OUTPUT.PUT_LINE("Test Value: " || lv_test_txt);
8 END;
9 /
Test Value: $54,321.00
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
The TO_CHAR() function converts the expression into character data.
Syntax: TO_CHAR(<expression> [,[FORMAT] [,NLS_PARAM]] )
SQL>
SQL> SELECT
2 LENGTHB(NCHR(216)) national,
3 LENGTHB(TO_CHAR(NCHR(216))) converted
4 FROM dual;
NATIONAL CONVERTED
---------- ----------
2 1
to_char(bdate,"fmMonth ddth, yyyy")
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> select ename
2 , to_char(bdate,"fmMonth ddth, yyyy")
3 from emp;
Enter...
Tom December 17th, 1965
Jack Februari 20th, 1961
Wil Februari 22nd, 1962
Jane April 2nd, 1967
Mary September 28th, 1956
Black November 1st, 1963
Chris Juni 9th, 1965
Smart November 26th, 1959
Peter November 17th, 1952
Take September 28th, 1968
Ana December 30th, 1966
Jane December 3rd, 1969
Fake Februari 13th, 1959
Mike Januari 23rd, 1962
14 rows selected.
SQL> drop table emp;
Table dropped.
to_char(round(sqrt(sal),2),"9999.99")
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> select ename, sal, to_char(round(sqrt(sal),2),"9999.99")
2 "Bonus" from emp;
ENAME SAL Bonus
---------- ---------- --------
SMITH 800 28.28
ALLEN 1600 40.00
WARD 1250 35.36
JONES 2975 54.54
MARTIN 1250 35.36
BLAKE 2850 53.39
CLARK 2450 49.50
SCOTT 3000 54.77
KING 5000 70.71
TURNER 1500 38.73
ADAMS 1100 33.17
ENAME SAL Bonus
---------- ---------- --------
JAMES 950 30.82
FORD 3000 54.77
MILLER 1300 36.06
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
to_char(sysdate,"DAY dy Dy") as day, to_char(sysdate,"MONTH mon") as month
SQL>
SQL> select to_char(sysdate,"DAY dy Dy") as day
2 , to_char(sysdate,"MONTH mon") as month
3 from dual;
DAY MONTH
--------------- -------------
MAANDAG ma Ma OKTOBER okt
SQL>
SQL>
to_char(sysdate, "Day", "nls_date_language=Dutch")
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
--------- ---------
Maandag Maandag
SQL>
SQL>
to_char(sysdate,"hh24:mi:ss") as time
SQL> select sysdate as today
2 , to_char(sysdate,"hh24:mi:ss") as time
3 , to_char(to_date("01/01/2006","dd/mm/yyyy")
4 ,""is on "Day") as new_year_2006
5 from dual;
TODAY TIME NEW_YEAR_2006
---------- -------- ---------------
26-10-2009 10:03:46 is on Zondag
SQL>
SQL>
Use to_char function to init an text value
SQL>
SQL> DECLARE
2 lv_current_date DATE := SYSDATE;
3 lv_current_month_txt VARCHAR(3) := TO_CHAR(SYSDATE, "MON");
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE("Current Date: " ||lv_current_date);
6 DBMS_OUTPUT.PUT_LINE("Current Month: " ||lv_current_month_txt);
7 END;
8 /
Current Date: 19-JUN-08
Current Month: JUN
PL/SQL procedure successfully completed.
SQL>
SQL>