戳這里，第二彈 → oracle常用的函數（二） 之 nvl、decode、length…

前言

工作中開始接觸了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;