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
<source lang="sql">
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>
</source>
convert a negative number into one with a trailing "minus sign,"
<source lang="sql">
SQL> SQL> SELECT
2 TO_CHAR(-1234,"9999MI") result 3 FROM dual;
RESUL
1234- SQL>
</source>
Pass variable into to_char function
<source lang="sql">
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>
</source>
The TO_CHAR() function converts the expression into character data.
<source lang="sql">
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 </source>
to_char(bdate,"fmMonth ddth, yyyy")
<source lang="sql">
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.
</source>
to_char(round(sqrt(sal),2),"9999.99")
<source lang="sql">
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.
</source>
to_char(sysdate,"DAY dy Dy") as day, to_char(sysdate,"MONTH mon") as month
<source lang="sql">
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>
</source>
to_char(sysdate, "Day", "nls_date_language=Dutch")
<source lang="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
---------
Maandag Maandag SQL> SQL>
</source>
to_char(sysdate,"hh24:mi:ss") as time
<source lang="sql">
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>
</source>
Use to_char function to init an text value
<source lang="sql">
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>
</source>
