SQL-转换函数

4. 使用转换函数和条件表达式

隐式数据类型转换, Oracle Server 可以自动执行以下转换:

• VARCHAR2 或CHAR —> NUMBER
• VARCHAR2 或CHAR —> DATE
• NUMBER —> VARCHAR2 或CHAR
• DATE —> VARCHAR2 或CHAR

注： number 与date 不能隐式转换

-- 日期可以和字符串自动转换，但是需要与数据库定义的格式一致
SQL> show parameter nls

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RR


SQL> select employee_id, first_name, hire_date from employees where hire_date = '30-JAN-04';

EMPLOYEE_ID FIRST_NAME           HIRE_DATE
----------- -------------------- ---------
        156 Janette              30-JAN-04



-------------------------------------------------------
TO_CHAR(date, 'format_model'): 将指定的日期按照某种格式转换为字符串
-- • 必须放在单引号内
-- • 区分大小写
-- • 可以包含任何有效的日期格式元素
-- • 具有一个fm 元素，用于删除填充的空格或隐藏前导零
-- • 与日期值之间用逗号分隔
/*
YYYY 用数字表示的完整年份
YEAR 拼写出的年份（用英文表示）
DY 一周中某日的三个字母缩写
DAY 一周中某日的完整名称
MM 月份的两位数值
MONTH 月份的完整名称
MON 月份的三个字母缩写
DD 用数字表示的月份中某日
FM 去掉查询后的前导0

HH24:MI:SS AM 15:45:32 PM
DD "of" MONTH 12 of OCTOBER
ddspth fourteenth
*/

select last_name,
to_char(hire_date, 'fmDD Month YYYY')
as HIREDATE
from employees;

LAST_NAME                 HIREDATE
------------------------- -----------------
OConnell                  21 June 2007
Grant                     13 January 2008

select sysdate, 
       to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') date1,
       to_char(sysdate,'year-mm-dd HH:MI:SS') date_year,
       to_char(sysdate,'yy-month-dd HH:MI:SS') date_month,
       to_char(sysdate,'yy-mm-dd DY HH:MI:SS') date_num
from dual;

SYSDATE             DATE1               DATE_YEAR                    
------------------- ------------------- -----------------------------
2016-11-02 01:27:05 2016-11-02 01:27:05 twenty sixteen-11-02 01:27:05

 DATE_MONTH               DATE_NUM                 DATE_FM
 ------------------------ ------------------------ ------------------
 16-november -02 01:27:05 16-11-02 WED 01:27:05    16-11-2 WED 1:27:5
 
 #查询2月份雇员的员工
 SQL> select employee_id, first_name, hire_date from employees where to_char(hire_date,'MM')=02;

EMPLOYEE_ID FIRST_NAME                HIRE_DATE
----------- ------------------------- -------------------
        201 Michael                   2004-02-17 00:00:00
        183 Girard                    2008-02-03 00:00:00
        185 Alexis                    2005-02-20 00:00:00
        301 Den                       1999-02-03 00:00:00
        302 test1                     2014-02-01 00:00:00


/*
TO_CHAR(number, 'format_model')： 将指定的数字按照某种格式转换为字符串

9 代表一个数字
0 强制显示零
$ 放置一个浮动的美元符号
L 使用浮动的本地货币符号
. 显示小数点
, 显示作为千位指示符的逗号
*/

select TO_CHAR(salary, '$99,999.00') SALARY
from employees
where last_name = 'Ernst';

SALARY
-----------
  $6,000.00

# 使用TO_NUMBER 函数可将字符串转换为数字格式：  字符串转换数字可以自动转换
TO_NUMBER(char[, 'format_model'])

# 使用TO_DATE 函数可将字符串转换为日期格式： 在查询的时候使用的不多，主要用在更新数据时
TO_DATE(char[, 'format_model'])

select last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
from employees
where hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

to_char()：可以匹配2008年的所有记录
SQL> select employee_id, first_name, to_char(hire_date, 'yyyy-mm-dd')  from employees where to_char(hire_date, 'yyyy') = 2008;

EMPLOYEE_ID FIRST_NAME           TO_CHAR(HI
----------- -------------------- ----------
        199 Douglas              2008-01-13
        183 Girard               2008-02-03
        128 Steven               2008-03-08
        136 Hazel                2008-02-06
        149 Eleni                2008-01-29


to_date()：只可以匹配确切的日期
SQL> select employee_id, first_name, to_char(hire_date, 'yyyy-mm-dd')  from employees where hire_date = to_date(2008,'yyyy');

no rows selected

SQL> select employee_id, first_name, to_char(hire_date, 'yyyy-mm-dd')  from employees where hire_date = to_date(20080113,'yyyymmdd');

EMPLOYEE_ID FIRST_NAME           TO_CHAR(HI
----------- -------------------- ----------
        199 Douglas              2008-01-13


-- 嵌套函数
/*
• 单行函数可以嵌套到任意层。
• 嵌套函数的计算顺序是从最内层到最外层。
F3(F2(F1(col,arg1),arg2),arg3)
*/

select last_name,
upper(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
from employees
where department_id = 60;

LAST_NAME                 UPPER(CONCA
------------------------- -----------
Hunold                    HUNOLD_US
Ernst                     ERNST_US
Austin                    AUSTIN_US
Pataballa                 PATABALL_US
Lorentz                   LORENTZ_US

select TO_CHAR (ROUND ( (salary / 7), 2),
                '99G999D99',
                'NLS_NUMERIC_CHARACTERS = '',.'' ')
          "Formatted Salary"
  from employees;

Formatted
----------
    371,43
    371,43
  1.185,71