Hàm TO_CHAR – Oracle/PLSQL
Hàm TO_CHAR – Oracle/PLSQL là một trong các hàm trong danh sách được cung cấp sẵn cho người sử dụng khi thao tác với hệ quản trị cơ sở dữ liệu Oracle.
Hàm TO_CHAR – Oracle/PLSQL – Chức năng
The Oracle/PLSQL TO_CHAR function converts a number or date to a string.
Hàm TO_CHAR – Oracle/PLSQL – Cú pháp
Cú pháp Hàm TO_CHAR trong Oracle/PLSQL như sau :
TO_CHAR( value [, format_mask] [, nls_language] )
Tham số
- value
- A number or date that will be converted to a string.
- format_mask
- Optional. This is the format that will be used to convert value to a string.
- nls_language
- Optional. This is the nls language used to convert value to a string.
Hàm TO_CHAR – Oracle/PLSQL – Phạm vi
Hàm TO_CHAR có thể sẵn sàng sử dụng trong Oracle/PLSQL với các phiên bản Oracle như sau:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Hàm TO_CHAR – Oracle/PLSQL – Ví dụ
Dưới đây là các ví dụ minh họa cách sử dụng hàm TO_CHAR trong Oracle/PLSQL như sau:
Let’s look at some Oracle TO_CHAR function examples and explore how to use the TO_CHAR function in Oracle/PLSQL.
Examples with Numbers
For example:
The following are number examples for the TO_CHAR function.
TO_CHAR(1210.73, '9999.9') Result: ' 1210.7' TO_CHAR(-1210.73, '9999.9') Result: '-1210.7' TO_CHAR(1210.73, '9,999.99') Result: ' 1,210.73' TO_CHAR(1210.73, '$9,999.00') Result: ' $1,210.73' TO_CHAR(21, '000099') Result: ' 000021'
Examples with Dates
The following is a list of valid parameters when the TO_CHAR function is used to convert a date to a string. These parameters can be used in many combinations.
Parameter | Explanation |
---|---|
YEAR | Year, spelled out |
YYYY | 4-digit year |
YYY YY Y |
Last 3, 2, or 1 digit(s) of year. |
IYY IY I |
Last 3, 2, or 1 digit(s) of ISO year. |
IYYY | 4-digit year based on the ISO standard |
Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
MM | Month (01-12; JAN = 01). |
MON | Abbreviated name of month. |
MONTH | Name of month, padded with blanks to length of 9 characters. |
RM | Roman numeral month (I-XII; JAN = I). |
WW | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
W | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
IW | Week of year (1-52 or 1-53) based on the ISO standard. |
D | Day of week (1-7). |
DAY | Name of day. |
DD | Day of month (1-31). |
DDD | Day of year (1-366). |
DY | Abbreviated name of day. |
J | Julian day; the number of days since January 1, 4712 BC. |
HH | Hour of day (1-12). |
HH12 | Hour of day (1-12). |
HH24 | Hour of day (0-23). |
MI | Minute (0-59). |
SS | Second (0-59). |
SSSSS | Seconds past midnight (0-86399). |
FF | Fractional seconds. |
The following are date examples for the TO_CHAR function.
TO_CHAR(sysdate, 'yyyy/mm/dd') Result: '2003/07/09' TO_CHAR(sysdate, 'Month DD, YYYY') Result: 'July 09, 2003' TO_CHAR(sysdate, 'FMMonth DD, YYYY') Result: 'July 9, 2003' TO_CHAR(sysdate, 'MON DDth, YYYY') Result: 'JUL 09TH, 2003' TO_CHAR(sysdate, 'FMMON DDth, YYYY') Result: 'JUL 9TH, 2003' TO_CHAR(sysdate, 'FMMon ddth, YYYY') Result: 'Jul 9th, 2003'
You will notice that in some TO_CHAR function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.
TO_CHAR(sysdate, 'FMMonth DD, YYYY') Result: 'July 9, 2003' TO_CHAR(sysdate, 'FMMON DDth, YYYY') Result: 'JUL 9TH, 2003' TO_CHAR(sysdate, 'FMMon ddth, YYYY') Result: 'Jul 9th, 2003'
The zeros have been suppressed so that the day component shows as "9" as opposed to "09".
Tham khảo các hàm built-in trong Oracle/PLSQL khác
STT | Tên hàm | Phạm vi | Chi tiết |
---|---|---|---|
1 | ABS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
2 | ACOS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
3 | ADD_MONTHS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
4 | ASCII | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
5 | ASCIISTR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
6 | ASIN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
7 | ATAN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
8 | ATAN2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
9 | AVG | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
10 | BFILENAME | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
11 | BIN_TO_NUM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
12 | BITAND | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
13 | CARDINALITY | Oracle 12c, Oracle 11g, Oracle 10g | xem |
14 | CASE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
15 | CEIL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
16 | CHR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
17 | COALESCE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
18 | CONCAT | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
19 | Concat with || | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
20 | CORR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
21 | COS | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
22 | COSH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
23 | COVAR_POP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
24 | COVAR_SAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
25 | CUME_DIST | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
26 | CURRENT_DATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
27 | CURRENT_TIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
28 | DBTIMEZONE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
29 | DECODE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
30 | DECOMPOSE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
31 | DENSE_RANK | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
32 | DUMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
33 | EMPTY_BLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
34 | EMPTY_CLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
35 | EXP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
36 | EXTRACT | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
37 | FIRST_VALUE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
38 | FLOOR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
39 | FROM_TZ | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
40 | GREATEST | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
41 | GROUP_ID | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
42 | HEXTORAW | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
43 | INITCAP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
44 | INSTR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
45 | INSTR2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
46 | INSTR4 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
47 | INSTRB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
48 | INSTRC | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
49 | LAG | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
50 | LAST_DAY | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
51 | LAST_VALUE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
52 | LEAD | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
53 | LEAST | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
54 | LENGTH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
55 | LENGTH2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
56 | LENGTH4 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
57 | LENGTHB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
58 | LENGTHC | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
59 | LISTAGG | Oracle 12c, Oracle 11g Release 2 | xem |
60 | LN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
61 | LOCALTIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
62 | LOG | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
63 | LOWER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
64 | LPAD | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
65 | LTRIM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
66 | MAX | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
67 | MEDIAN | Oracle 12c, Oracle 11g, Oracle 10g | xem |
68 | MIN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
69 | MONTHS_BETWEEN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
70 | NANVL | Oracle 12c, Oracle 11g, Oracle 10g | xem |
71 | NEW_TIME | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
72 | NEXT_DAY | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
73 | NTH_VALUE | Oracle 12c, Oracle 11g Release 2 | xem |
74 | NULLIF | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
75 | NUMTODSINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
76 | NUMTOYMINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
77 | NVL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
78 | NVL2 | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
79 | POWER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
80 | RANK | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
81 | REGEXP_COUNT | Oracle 12c, Oracle 11g | xem |
82 | REGEXP_INSTR | Oracle 12c, Oracle 11g, Oracle 10g | xem |
83 | REGEXP_REPLACE | Oracle 12c, Oracle 11g, Oracle 10g | xem |
84 | REGEXP_SUBSTR | Oracle 12c, Oracle 11g, Oracle 10g | xem |
85 | REPLACE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
86 | ROUND (numbers) | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
87 | ROWNUM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
88 | RPAD | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
89 | RTRIM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
90 | SESSIONTIMEZONE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
91 | SIGN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
92 | SIN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
93 | SINH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
94 | SQRT | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
95 | STDDEV | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
96 | SUBSTR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
97 | SUM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
98 | SYSDATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
99 | SYSTIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
100 | TAN | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
101 | TANH | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
102 | TO_CHAR | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
103 | TO_CLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
104 | TO_DATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
105 | TO_DSINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
106 | TO_LOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
107 | TO_MULTI_BYTE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
108 | TO_NCLOB | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
109 | TO_NUMBER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
110 | TO_SINGLE_BYTE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
111 | TO_TIMESTAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
112 | TO_TIMESTAMP_TZ | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
113 | TO_YMINTERVAL | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i | xem |
114 | TRANSLATE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
115 | TRIM | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
116 | TRUNC (dates) | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
117 | TRUNC (numbers) | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
118 | UID | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
119 | UPPER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
120 | USER | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
121 | USERENV | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
122 | VAR_POP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
123 | VAR_SAMP | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
124 | VARIANCE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
125 | VSIZE | Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i | xem |
Blog được lập ra với mục đích để chia sẻ đề thi tới toàn bộ học sinh, sinh viên ở Việt Nam. Trong quá trình đăng bài không thể tránh được những sai sót, Nếu các bạn phát hiện ra sai sót có thể comment ở bên dưới hoặc có đề hay muốn chia sẻ tới tất cả mọi người có thể gửi thông tin vào email [email protected]
Mít&Béo