oracle单行函数之转换函数

1、单行函数分类：一行记录，返回一行结果；

四种转换、三个函数：

2、to_char(date,‘fmt’)：将一个date日期，转换为’fmt’日期格式的字符串显示。

1）常见的日期格式控制符

2）数据源如下

3）案例如下

① 查询1987年入职的员工(姓名，入职日期)。

SQL> select ename,hiredate 
  2  from emp
  3  where to_char(hiredate,'YYYY')='1987';
 
ENAME      HIREDATE
---------- -----------
SCOTT      1987/4/19
ADAMS      1987/5/23

② 查询1981年2月入职的员工(姓名，入职日期)。

SQL> select ename,hiredate
  2  from emp
  3  where to_char(hiredate,'YYYY-mm')='1981-02';
 
ENAME      HIREDATE
---------- -----------
ALLEN      1981/2/20
WARD       1981/2/22

• 这里需要注意的是，对于月份只能是2个m，同时后面的筛选条件必须写成’1981-02’，而不能是’1981-2’。

③ 返回某个日期是哪一年、哪一月、哪一天、哪个季度。

-- 返回日期中的年份
SQL> select to_char(sysdate,'yyyy') from dual;
 
TO_CHAR(SYSDATE,'YYYY')
-----------------------
2019
 -- 返回日期中的月份
SQL> select to_char(sysdate,'mm') from dual;
 
TO_CHAR(SYSDATE,'MM')
---------------------
12
SQL> select to_char(sysdate,'dd') from dual;
 -- 返回日期中的日
TO_CHAR(SYSDATE,'DD')
---------------------
16
SQL> select to_char(sysdate,'d')-1 from dual;
 -- 返回日期是星期几：西方星期天表示周一，因此，这里减1
TO_CHAR(SYSDATE,'D')-1
----------------------
1
SQL> select to_char(sysdate,'q') from dual;
 -- 返回日期是第几个季度
TO_CHAR(SYSDATE,'Q')
--------------------
4

3、to_number(str,‘fmt’)：将某种格式的数值字符str，转换为数值显示。

1）常用的转换符如下

2）案例如下

• 为了防止弄混淆：左侧要转换的数字字符串是什么格式，右侧就使用一致相同的格式去进行匹配。

SQL> select to_number('$123456.123456','$999999.000000') from dual;
 
TO_NUMBER('$123456.123456','$9
------------------------------
                 123456.123456
 
SQL> select to_number('￥123456.123456','L999999.000000') from dual;
 
 TO_NUMBER('￥123456.123456','L
------------------------------
                 123456.123456
SQL> select to_number('$123,456.123456','$999,999.000000') from dual;
 
TO_NUMBER('$123,456.123456','$
------------------------------
                 123456.123456

• 当要转换的数字字符串长短不一，就是用最长的那个数字字符串的匹配格式，去进行匹配。

SQL> select to_number('$123.123','$999,999.000000') from dual;
 
TO_NUMBER('$123.123','$999,999
------------------------------
                       123.123
 
SQL> select to_number('$123,456.123456','$999,999.000000') from dual;
 
TO_NUMBER('$123,456.123456','$
------------------------------
                 123456.123456

• 当要转换的数字字符串的格式和长度(匹配格式的长度只能比原来数字的长度要长，不管是整数部分，还是小数部分。)，与你所写的匹配格式不一致时，就会报错。

SQL> select to_number('￥123.123','$999.000') from dual;
 
select to_number('￥123.123','$999.000') from dual
 
ORA-01722: 无效数字
 
SQL> select to_number('$123.123','$999.00') from dual;
 
select to_number('$123.123','$999.00') from dual
 
ORA-01722: 无效数字
 
SQL> select to_number('$123.123','$99.00') from dual;
 
select to_number('$123.123','$99.00') from dual
 
ORA-01722: 无效数字

4、to_date()：把任何不规则的日期格式的字符型数据，转换为日期型数据。

• 将不规则的类似于日期格式的字符串数据，转换为规整的日期格式的数据。

SQL> select to_date('2018-02-10','YYYY-mm-dd') from dual;
 
TO_DATE('2018-02-10','YYYY-MM-
------------------------------
2018/2/10
 
SQL> select to_date('2018.02.10','YYYY.mm.dd') from dual;
 
TO_DATE('2018.02.10','YYYY.MM.
------------------------------
2018/2/10
 
SQL> select to_date('2018.02 10','YYYY.mm dd') from dual;
 
TO_DATE('2018.0210','YYYY.MMDD
------------------------------
2018/2/10