oracle常用的函数（一） 之 to_char、to_date

前言

工作中开始接触了oracle，在使用的时候经常用到一些函数，再次做个总结，方便后续查看与使用

to_char

该函数主要用于将数据类型转换为字符类型:
1、将数值、日期等类型转换为字符串
2、根据指定的格式模型对结果进行格式化

基本语法

TO_CHAR(value, format_model, nls_params)

• value：要转换的值，可以是日期或数字
• format_model：格式模型，用于指定转换后的字符串格式
• nls_params：（可选）用于指定语言环境参数，如日期语言、数字分组符号等

格式模型

格式模型介绍

在oracle的to_char函数中，常用的一个格式修饰符是FM。FM用于去除格式化后的字符串中的前导空格或尾随零。结合数字格式模型，FM99999999999999999999999990.00的作用是将数字格式化为指定的格式

• FM：Fill Model，表示“填充模式”，用于去除格式化后的字符串中的前导空格或尾随零
• 9：表示数字字符，如果该位置没有数字，则显示为空格
• 0：表示数字字符，如果该位置没有数字，则显示为0
• .：表示小数点
• 00：表示小数部分，即使数字没有小数部分，也会显示两位小数
• L：货币符号
• D：小数点
• G：分组分隔符
• PR：如果数字为负，则在字符串前后加上尖括号
• RN：罗马数字
• TH：数字的序数（如1st，2nd，3rd等）

FM99999999999999999999999990.00 的作用是将数字格式化为最多 27 位整数和 2 位小数的格式，同时去除不必要的前导空格或尾随零。这种格式化方式特别适用于需要精确控制数字显示格式的场景，例如财务报表或数据导出。

无FM示例

• 格式模式中的数字占位符（如9或0）的数量与实际数字的位数完全匹配

  select to_char(1234,'9999') as formatted_number from dual;
  

  输出结果：1234（没有空格）
  应为格式模型9999表示数字最多可以有4位，实际数字正好是4位，所以没有空格

• 实际数字的位数大于格式模式中的占位符

  select to_char(12345,'9999') as formatted_number from dual;
  

  输出结果：12345（没有空格）

• 使用0作为占位符，不是9，此时不会出现空格，会用0填充

  select to_char(123,'0000') as formatted_number from dual;
  

  输出结果：0123（前边用0填充）

• 格式模型中的数字占位符数量大于实际数字的位数，那么会在数字前填充空格，以保持格式模型的宽度一致

  select to_char(123,'99999') as formatted_number from dual;
  

  输出结果： 123（前边有两个空格）
  格式模型99999表示数字最多可以有5位，实际数字123只有三位，所以前边会填充三个空格

使用FM

• 较小的数字，前导空格被去除

  select to_char(123,'FM9999999.00') as formatted_number from dual;
  

  输出结果：123.00（没有空格）
  使用 FM 修饰符后，前导空格会被去除

• 负数

  SELECT TO_CHAR(-123456, 'FM99999999990.00') AS formatted_number FROM dual;
  

  输出结果：-123456.00

输出货币

 SELECT TO_CHAR(123456.78, 'L999,999.99') AS formatted_number FROM dual;

输出结果：$123,456.78（具体货币符号取决于语言环境）

负数输出尖括号

 SELECT TO_CHAR(-123456.78, '999,999.99PR') AS formatted_number FROM dual;

输出结果：<123,456.78>

将日期格式化

当value是日期类型时，to_char函数可以将日期格式化为指定的字符串格式。

• 常用日期格式

  YYYY：四位年份
  MM：两位月份
  DD：两位日期
  HH24：24小时制的小时
  MI：分钟
  SS：秒
  AM 或 PM：上午或下午
  DY：星期几的缩写（如 Mon、Tue 等）
  DAY：星期几的全称（如 Monday、Tuesday 等）
  MON：月份的缩写（如 Jan、Feb 等）
  MONTH：月份的全称（如 January、February 等）

• 筛选特定日期格式的记录

  # 将日期格式化为 YYYY-MM-DD 格式。
  select employee_id、name from employees 
  where to_char(hire_date,'YYYY-MM-DD')>'2023-06-23'
  

  1）将hire_date转换为格式化的字符串
  2）筛选出晚于2023年6月23号入职的员工

• 筛选特定星期几的记录

  # 将日期格式化为 YYYY-MM-DD 格式。
  select employee_id、name from employees 
  where to_char(hire_date,'DY','NLS_DATE_LANGUAGE=ENGLISH') <>'MON'
  

  1）将hire_date转换为星期几的缩写
  2）NLS_DATE_LANGUAGE=ENGLISH确保星期几的缩写使用英文
  3）筛选出不是星期一的数据

• 筛选特定日期范围的记录

  # 将日期格式化为 YYYY-MM-DD 格式。
  select employee_id、name from employees 
  where to_char(hire_date,'YYYY-MM-DD') not between '2023-01-01' and '2023-12-31'
  

  1）将hire_date转换为格式化的字符串
  2）筛选出不在2023年入职的员工

将数字格式化为带有货币符号和千位分隔符的格式

# 将数字格式化为带有货币符号和千位分隔符的格式。
SELECT TO_CHAR(salary, 'L999,999.99') AS formatted_salary
FROM employees;

如果salary的值是123456.78
输出结果：$123,456.78货币符号$是根据数据库的默认货币符号来显示的

总结

• 如果字段是时间格式，然后我们需要筛选

  select employee_id、name from employees 
  where to_char(hire_date,'YYYY-MM-DD')>'2023-06-23'
  

• 如果需要显示特定的货币符号

  • 查询货币符号 并 设置指定的货币符号

    select * from NLS_DATEBASE_PARAMETERS WHERE PARAMETERS ='NLS_CURRENCY'
    

    ALTER SESSION SET NLS_CURRENCY='￥'
    

  或

  • 查询时直接设置货币符号，这样无论数据库默认货币符号是什么，都会显示为￥

    select to_char(salary,'L999,999.99','NLS_CURRENCY = ￥') as formatted_salary
    from employees;
    

• 格式模式使用，例如需要去除前后空格并不需要小数部分

  SELECT TO_CHAR(123456.78, 'FM99999999') AS formatted_number FROM dual;
  

• 性能

  对于大数据量的表，使用to_char函数可能会对性能产生一定的影响

  • 1、to_char函数需要再查询执行过程中动态计算并转换数据类型，从而会增加cpu的消耗和内存的占用
  • 2、在where子句中使用to_char函数对导致数据库无法使用索引，因为格式化后的值与索引中的原始值不匹配

to_date

TO_DATE 函数用于将字符串转换为日期类型，并可以指定格式

语法

TO_DATE(string, format)

语法示例

将字符串 ‘2024-06-12’ 转换为日期类型

SELECT TO_DATE('2024-06-12', 'YYYY-MM-DD') AS hire_date
FROM dual;