SysVN tiếp tục giới thiệu bài viết “cách sử dụng Conversion Functions và những biểu thức điều kiện trong Oracle SQL”. Mời các bạn theo dõi!
- Mục tiêu
Sau khi hiểu rõ bài viết này các bạn có thể:
- Mô tả những kiểu khác nhau của những hàm chuyển đổi (Conversion Functions).
- Cách sử dụng những hàm chuyển đổi (Conversion Functions): TO_CHAR, TO_NUMBER vàTO_DATE.
- Gán những biểu thức điều kiện vào mệnh đề SELECT
2. Kiểu chuyển đổi dữ liệu tường minh và ngầm định
Kiểu chuyển đổi dữ liệu gồm 2 loại: tường minh (explicit) và ngầm định (implicit)
a. Chuyển đổi dữ liệu kiểu ngầm định
Oracle Server tự động chuyển đổi dữ liệu theo những kiểu sau
| From | To |
| VARCHAR2 hoặc CHAR | NUMBER |
| VARCHAR2 hoặc CHAR | DATE |
Oracle tự động thực hiện chuyển đổi dữ liệu trong một biểu thức. Ví dụ, hire_date > ’01-JAN-90′, kết quả chuyển đổi ngầm định từ chuỗi ’01-JAN-90′ sang kiểu date. Do đó, giá trị VARCHAR2 hoặc CHAR có thể tự động chuyển đổi thành kiểu number, date trong biểu thức.
Ngoài ra Oracle còn tự động chuyển đổi theo bảng sau:
| From | To |
| NUMBER | VARCHAR2 or CHAR |
| DATE | VARCHAR2 or CHAR |
Chú ý: Chuyển đổi từ Char sang Number thành công nếu chuỗi ký tự là số.
b. Chuyển đổi dữ liệu kiểu tường minh
3. Cách sử dụng TO_CHAR, TO_NUMBER, TO_DATE
a. Sử dụng TO_CHAR với date
Cú pháp: TO_CHAR(date, ‘format_model‘)
- Sử dụng TO_CHAR để chuyển đổi giá trị ngày tháng thành kiểu dữ liệu VARCHAR2 trong định dạng format_model .
- format_model: được đặt trong dấu nháy đơn, phân biệt chữ hoa thường, có thể bất kỳ phần tử định dạng ngày, sử dụng fm để loại bỏ khoảng trắng hoặc zero.
Ví dụ:
SELECT employee_id, TO_CHAR(hire_date, ‘MM/YY’) Month_Hired
FROM employees
WHERE last_name = ‘Higgins’;
EMPLOYEE_ID MONTH
———– —–
205 06/02
Những thành phần trong định dạng date
| Element | Result |
| YYYY | Ví du: 2007 |
| YEAR | Ví dụ: TWO THOUSAND SEVEN |
| MM | 2 giá trị số của tháng. Ví dụ tháng 12, MM là 12 |
| MONTH | Tên đầy đủ của tháng. Ví dụ: JUNE |
| MON | 3 ký tự đầu của tháng. Ví dụ: JUN |
| DY | 3 ký tự ngày của tuần. Ví dụ: FRI |
| DAY | Tên đầy đủ ngày của tuần. Ví dụ: FRIDAY |
| DD | Số ngày của tháng. Ví dụ: 30 |
Định dạng thời gian của ngày
| Element | Description |
| AM, PM hoặc A.M, P.M | Ký hiệu thời gian sáng, chiều |
| HH hoặc HH12 | Hệ giờ 12 |
| HH24 | Hệ giờ 24 |
| MI | Phút (0-59) |
| SS | Giây (0-59) |
| SSSSS | Số giây từ 0 giờ |
| TH | Số thứ tự. Ví dụ 4TH hoặc 21ST |
| SP | Chuyển số sang dạng phát âm tiếng Anh (chữ số) |
| SPTH | Chuyển số sang dạng phát âm tiếng Anh (số thứ tự) |
Một vài ví dụ
SELECT last_name,hire_date,
TO_CHAR(hire_date, ‘fmDDsp MON YYYY’)
AS HIREDATE, TO_CHAR(hire_date, ‘fmDDspth MM YEAR’)
FROM employees;
SELECT last_name,hire_date,
TO_CHAR(hire_date,’fmDdspth “of” Month YYYY fmHH:MI:SS PM’) HIREDATE
FROM employees;
b. Sử dụng TO_CHAR với number
Cú pháp: TO_CHAR(number, ‘format_model‘)
Bảng mô tả format_model
| Element | Description | Example | Result |
| 9 | Đại diện cho một chữ số | 999999 | 1234 |
| 0 | Hiển thị một số 0 | 099999 | 001234 |
| $ | Hiển thị dấu $ | $999999 | $1234 |
| L | Hiển thị ký tự tiền tệ | 999,999.00L | 1,234.00$ |
| Dấu chấm (.) | Hiển thị dấu thập phân tại vị trí xác định | 999999.99 | 1234.00 |
| Dấu phẩy (,) | Hiển thị dấu , phần ngàn tại vị trí xác định | 999,999 | 1,234 |
| G | Hiển thị dấu phần ngàn tại vị trí xác định | 9G999 | 1,234 |
| S | Hiển thị dấu +/- xác định số dương, âm | S9999 | -1234 hoặc +1234 |
| D | Hiển thị dấu thập phân tại vị trí xác định | 9999D99 | 1234.00 |
| B | Hiển thị giá trị zero như khoảng trắng, không phải 0 | B9999.99 | 1234.00 |
Một vài ví dụ
SELECT salary, TO_CHAR(salary, ‘$99,999.00’) SALARY
FROM employees;
SELECT TO_CHAR(1234,’999999′) FROM dual;
TO_CHAR
——-
1234
SELECT TO_CHAR(1234,’999,999.00L’) FROM dual;
TO_CHAR(1234,’999,999
———————
1,234.00$
c. Sử dụng hàm TO_NUMBER
TO_NUMBER dùng để chuyển đổi một chuỗi thành định dạng số.
Cú pháp: TO_NUMBER(char[, ‘format_model‘])
Chú ý: Chuyển đổi từ Char sang Number thành công nếu chuỗi ký tự là số.
Ví dụ:
SELECT TO_NUMBER(‘123,456.12’ , ‘999999.99’) FROM dual;
TO_NUMBER(‘123,456.12′,’999999.99’)
———————————–
123456.12
d. Sử dụng hàm TO_DATE
Cú pháp: TO_DATE(char[, ‘format_model‘])
TO_DATE dùng để chuyển 1 chuỗi dạng ngày tháng sang kiểu ngày tháng. Nếu muốn đảm bảo chuỗi nhập vào đúng với format kể cả khoảng trắng, dấu câu, sử dụng fx ở đầu chuỗi format. Khi phát hiện chuỗi nhập vào không đúng thì sẽ báo lỗi không convert được. Còn không dùng fx thì Oracle sẽ tự bỏ qua.
Ví dụ:
SELECT TO_DATE(‘MAY 24, 1999’, ‘MONTH DD, YYYY’) FROM dual;
TO_DATE(‘
———
24-MAY-99
e. Sử dụng TO_CHAR, TO_DATE với định dạng RR
Ví dụ:
SELECT last_name, TO_CHAR(hire_date, ‘DD-Mon-YYYY’)
FROM employees
WHERE hire_date < TO_DATE(’01-Jan-08′,’DD-Mon-RR’);
Tương tự, ta cũng có thể sử dụng sử dụng TO_CHAR, TO_DATE với định dạng YY
Vi dụ:
SELECT last_name, TO_CHAR(hire_date, ‘DD-Mon-YYYY’)
FROM employees
WHERE hire_date < TO_DATE(’01-Jan-08′,’DD-Mon-YY’);
f. Kết luận
Qua các ví dụ ta có thể thấy được format trong hàm TO_CHAR là format để định dạng chuỗi kết quả xuất ra, còn format trong hàm TO_NUMBER hay TO_DATE là để nhận dạng chuỗi nhập vào
