如何使用 SQL 函式處理資料

• 一、函式
  • 1.1 函式帶來的問題
• 二、使用函式
  • 2.1 文本處理函式
  • 2.2 日期和時間處理函式
  • 2.3 數值處理函式
• 三、小結

本文介紹什么是函式，DBMS 支持何種函式，以及如何使用這些函式；還將講解為什么 SQL 函式的使用可能會帶來問題，

一、函式

與大多數其他計算機語言一樣，SQL 也可以用函式來處理資料，函式一般是在資料上執行的，為資料的轉換和處理提供了方便，

SQL 如何創建計算欄位 中用來去掉字串尾的空格的 RTRIM() 就是一個函式，

1.1 函式帶來的問題

在繼續閱讀之前，你應該了解使用 SQL 函式所存在的問題，

與幾乎所有 DBMS 都等同地支持 SQL 陳述句（如 SELECT）不同，每一個 DBMS 都有特定的函式，

事實上，只有少數幾個函式被所有主要的 DBMS 等同地支持，雖然所有型別的函式一般都可以在每個 DBMS 中使用，但各個函式的名稱和語法可能極其不同，

為了說明可能存在的問題，表 1 列出了 3 個常用的函式及其在各個 DBMS 中的語法：

表 1 DBMS 函式的差異

可以看到，與 SQL 陳述句不一樣，SQL 函式不是可移植的，這意味著為特定 SQL 實作撰寫的代碼在其他實作中可能不能用，

可移植（portable）

所撰寫的代碼可以在多個系統上運行，

為了代碼的可移植，許多 SQL 程式員不贊成使用特定于實作的功能，雖然這樣做很有好處，但有的時候并不利于應用程式的性能，

如果不使用這些函式，撰寫某些應用程式代碼會很艱難，必須利用其他方法來實作 DBMS 可以非常有效完成的作業，

提示：是否應該使用函式？

現在，你面臨是否應該使用函式的選擇，決定權在你，使用或是不使用也沒有對錯之分，

如果你決定使用函式，應該保證做好代碼注釋，以便以后你自己（或其他人）能確切地知道這些 SQL 代碼的含義，

二、使用函式

大多數 SQL 實作支持以下型別的函式，

• 用于處理文本字串（如洗掉或填充值，轉換值為大寫或小寫）的文本函式，
• 用于在數值資料上進行算術操作（如回傳絕對值，進行代數運算）的數值函式，
• 用于處理日期和時間值并從這些值中提取特定成分（如回傳兩個日期之差，檢查日期有效性）的日期和時間函式，
• 用于生成美觀好懂的輸出內容的格式化函式（如用語言形式表達出日期，用貨幣符號和千分位表示金額），
• 回傳 DBMS 正使用的特殊資訊（如回傳用戶登錄資訊）的系統函式，

我們在 SQL 如何創建計算欄位 中看到函式用于 SELECT 后面的列名，但函式的作用不僅于此，

它還可以作為 SELECT 陳述句的其他成分，如在 WHERE 子句中使用，在其他 SQL 陳述句中使用等，后面會做更多的介紹，

2.1 文本處理函式

在 SQL 如何創建計算欄位 中，我們已經看過一個文本處理函式的例子，其中使用 RTRIM() 函式來去除列值右邊的空格，下面是另一個例子，這次使用的是 UPPER() 函式：

SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

輸出：

vend_name                       vend_name_upcase
---------------------------     ----------------------------
Bear Emporium                   BEAR EMPORIUM
Bears R Us                      BEARS R US
Doll House Inc.                 DOLL HOUSE INC.
Fun and Games                   FUN AND GAMES
Furball Inc.                    FURBALL INC.
Jouets et ours                  JOUETS ET OURS

可以看到，UPPER() 將文本轉換為大寫，因此本例子中每個供應商都列出兩次，第一次為 Vendors 表中存盤的值，第二次作為列 vend_name_upcase 轉換為大寫，

提示：大寫，小寫，大小寫混合

此時你應該已經知道 SQL 函式不區分大小寫，因此 upper(), UPPER(), Upper() 都可以，substr(), SUBSTR(), SubStr() 也都行，

隨你的喜好，不過注意保持風格一致，不要變來變去，否則你寫的程式代碼就不好讀了，

表 2 列出了一些常用的文本處理函式，

表 2 常用的文本處理函式

表 2 中的 SOUNDEX 需要做進一步的解釋，

SOUNDEX 是一個將任何文本串轉換為描述其語音表示的字母數字模式的演算法，

SOUNDEX 考慮了類似的發音字符和音節，使得能對字串進行發音比較而不是字母比較，

雖然 SOUNDEX 不是 SQL 概念，但多數 DBMS 都提供對 SOUNDEX 的支持，

說明：SOUNDEX 支持

PostgreSQL 不支持 SOUNDEX()，因此以下的例子不適用于這個 DBMS，

另外，如果在創建 SQLite 時使用了 SQLITE_SOUNDEX 編譯時選項，那么 SOUNDEX() 在 SQLite 中就可用，

因為 SQLITE_SOUNDEX 不是默認的編譯時選項，所以多數 SQLite 實作不支持 SOUNDEX()，

下面給出一個使用 SOUNDEX() 函式的例子，Customers 表中有一個顧客 Kids Place，其聯系名為 Michelle Green，但如果這是錯誤的輸入，此聯系名實際上應該是 Michael Green，該怎么辦呢？顯然，按正確的聯系名搜索不會回傳資料，如下所示：

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact = 'Michael Green';

輸出：

cust_name                      cust_contact
--------------------------     ----------------------------

現在試一下使用 SOUNDEX() 函式進行搜索，它匹配所有發音類似于 Michael Green 的聯系名：

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

輸出：

cust_name                      cust_contact
--------------------------     ----------------------------
Kids Place                     Michelle Green

在這個例子中，WHERE 子句使用 SOUNDEX() 函式把 cust_contact 列值和搜索字串轉換為它們的 SOUNDEX 值，

因為 Michael Green 和 Michelle Green 發音相似，所以它們的 SOUNDEX 值匹配，因此 WHERE 子句正確地過濾出了所需的資料，

2.2 日期和時間處理函式

日期和時間采用相應的資料型別存盤在表中，每種 DBMS 都有自己的特殊形式，日期和時間值以特殊的格式存盤，以便能快速和有效地排序或過濾，并且節省物理存盤空間，

應用程式一般不使用日期和時間的存盤格式，因此日期和時間函式總是用來讀取、統計和處理這些值，由于這個原因，日期和時間函式在 SQL 中具有重要的作用，遺憾的是，它們很不一致，可移植性最差，

我們舉個簡單的例子，來說明日期處理函式的用法，Orders 表中包含的訂單都帶有訂單日期，要檢索出某年的所有訂單，需要按訂單日期去找，但不需要完整日期，只要年份即可，

為在 SQL Server 中檢索 2020 年的所有訂單，可如下進行：

SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date) = 2020;

輸出：

order_num
-----------
20005
20006
20007
20008
20009

這個例子使用了 DATEPART() 函式，顧名思義，此函式回傳日期的某一部分，DATEPART()函式有兩個引數，它們分別是回傳的成分和從中回傳成分的日期，

在此例子中，DATEPART() 只從 order_date 列中回傳年份，通過與 2020 比較，WHERE 子句只過濾出此年份的訂單，

下面是使用名為 DATE_PART() 的類似函式的 PostgreSQL 版本：

SELECT order_num
FROM Orders
WHERE DATE_PART('year', order_date) = 2020;

Oracle 沒有 DATEPART() 函式，不過有幾個可用來完成相同檢索的日期處理函式，例如：

SELECT order_num
FROM Orders
WHERE EXTRACT(year FROM order_date) = 2020;

在這個例子中，EXTRACT() 函式用來提取日期的成分，year 表示提取哪個部分，回傳值再與 2020 進行比較，

提示：PostgreSQL 支持 Extract()

除了前面用到的 DatePart()，PostgreSQL 也支持 Extract() 函式，因而也能這么用，

完成相同作業的另一方法是使用 BETWEEN 運算子：

SELECT order_num
FROM Orders
WHERE order_date BETWEEN to_date('2020-01-01', 'yyyy-mm-dd')
 AND to_date('2020-12-31', 'yyyy-mm-dd');

在此例子中，Oracle 的 to_date() 函式用來將兩個字串轉換為日期，一個包含 2020 年 1 月 1 日，另一個包含 2020 年 12 月 31 日，

BETWEEN 運算子用來找出兩個日期之間的所有訂單，值得注意的是，相同的代碼在 SQL Server 中不起作用，因為它不支持 to_date() 函式，但是，如果用 DATEPART() 替換 to_date()，當然可以使用這種型別的陳述句，

DB2，MySQL 和 MariaDB 具有各種日期處理函式，但沒有 DATEPART()，DB2，MySQL 和 MariaDB 用戶可使用名為 YEAR() 的函式從日期中提取年份：

SELECT order_num
FROM Orders
WHERE YEAR(order_date) = 2020;

在 SQLite 中有個小技巧：

SELECT order_num
FROM Orders
WHERE strftime('%Y', order_date) = '2020';

這里給出的例子提取和使用日期的成分（年），按月份過濾，可以進行相同的處理，使用 AND 運算子可以進行年份和月份的比較，

DBMS 提供的功能遠不止簡單的日期成分提取，大多數 DBMS 具有比較日期、執行日期的運算、選擇日期格式等的函式，

但是，可以看到，不同 DBMS 的日期/時間處理函式可能不同，關于你的 DBMS 具體支持的日期/時間處理函式，請參閱相應的檔案，

2.3 數值處理函式

數值處理函式僅處理數值資料，這些函式一般主要用于代數、三角或幾何運算，因此不像字串或日期/時間處理函式使用那么頻繁，

具有諷刺意味的是，在主要 DBMS 的函式中，數值函式是最一致、最統一的函式，表 3 列出一些常用的數值處理函式，

表 3 常用數值處理函式

關于具體 DBMS 所支持的算術處理函式，請參閱相應的檔案，

三、小結

本文介紹了如何使用 SQL 的資料處理函式，雖然這些函式在格式化、處理和過濾資料中非常有用，但它們在各種 SQL 實作中很不一致，

原文鏈接：https://www.developerastrid.com/sql/sql-functions/

（完）

標籤：其他

上一篇：什么是 SQL 計算欄位，如何創建 SQL 計算欄位

下一篇：如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 匯總資料