其他函數

除了數學函數、字元串函數、日期函數之外，資料庫中還有其他一些函數，比如進行類型轉換的函數、進行非空邏輯判斷的函數等，這些函數也是非常重要的，是以在本節中我們将對這些函數進行介紹。

類型轉換

在使用sql語句的時候，我們使用的資料的類型不一定符合函數或者運算符的需要，比如函數需要整數類型的資料而我們使用的則是一個字元串，在一些情況下資料庫系統會替我們自動将字元串類型轉換為整數類型，這種轉換稱為隐式轉換。但是在有的情況下資料庫系統不會進行隐式轉換，這時就要使用類型轉換函數了，這種轉換稱為顯式轉換。使用類型轉換函數不僅可以保證類型轉換的正确性，而且可以提高資料處理的速度，是以應該使用顯式轉換，盡量避免使用隐式轉換。

在主流資料庫系統中都提供了類型轉換函數，下面分别進行介紹。

mysql

mysql中提供了cast()函數和convert()函數用于進行類型轉換，cast()是符合ansi sql99的函數，convert() 是符合odbc标準的函數，這兩個函數隻是參數的調用方式略有差異，其功能幾乎相同。這兩個函數的參數格式如下：

參數expression為待進行類型轉換的表達式，而type為轉換的目标類型，type可以是下面的任一個：

下面的sql語句分别示範以有符号整形、無符号整形、日期類型、時間類型為目标類型的資料轉換：

執行完畢我們就能在輸出結果中看到下面的執行結果：

mssqlserver

與mysql類似，mssqlserver中同樣提供了名稱為cast()和convert()兩個函數用于進行類型轉換，cast()是符合ansi sql99的函數，convert() 是符合odbc标準的函數。

與mysql中的convert()函數不同的是mssqlserver中的convert()函數參數順序正好與mysql中的convert()函數參數順序相反。這兩個函數的參數格式如下：

參數expression為待進行類型轉換的表達式，而type為轉換的目标類型，與mysql不同，mysqlserver中的目标類型幾乎可以是資料庫系統支援的任何類型。

下面的sql語句分别示範以整形、數值、日期時間類型為目标類型的資料轉換：

下面的sql語句用于将每個人的身份證後三位轉換為整數類型并且進行相關的計算：

oracle

oracle中也有一個名稱為convert()的函數，不過這個函數是用來進行字元集轉換的。oracle中不支援用做資料類型轉換的cast()和convert()兩個函數，它提供了針對性更強的類型to_char()、to_date()、to_number()等函數，這些函數可以将資料顯式的轉換為字元串類型、日期時間類型或者數值類型。oracle中還提供了hextoraw()、rawtohex()、to_multi_byte()、to_single_byte()等函數用于存儲格式的轉換。

下面我們将對這些函數進行分别介紹。

1) to_char()

to_char()函數用來将時間日期類型或者數值類型的資料轉換為字元串，其參數格式如下：

參數expression為待轉換的表達式，參數format為轉換後的字元串格式，參數format可以省略，如果省略參數format将會按照資料庫系統内置的轉換規則進行轉換。參數format的可以采用的格式非常豐富，具體可以參考oracle的聯機文檔。

下面的sql語句将出生日期和身高按照不同的格式轉換為字元串類型：

2) to_date()

to_date()函數用來将字元串轉換為時間類型，其參數格式如下：

參數expression為待轉換的表達式，參數format為轉換格式，參數format可以省略，如果省略參數format将會按照資料庫系統内置的轉換規則進行轉換。

下面的sql語句用于将字元串形式的資料按照特定的格式解析為日期類型：

3) to_number()

to_number()函數用來将字元串轉換為數值類型，其參數格式如下：

參數expression為待轉換的表達式，參數format為轉換格式，參數format可以省略，如果省略參數format将會按照資料庫系統内置的轉換規則進行轉換。參數format的可以采用的格式非常豐富，具體可以參考oracle的聯機文檔。

下面的sql語句用于将字元串形式的資料按照特定的格式解析為數值類型：

4) hextoraw()、rawtohex()

hextoraw()用于将十六進制格式的資料轉換為原始值，而rawtohex()函數用來将原始值轉換為十六進制格式的資料。例子如下：

5) to_multi_byte()、to_single_byte()

to_multi_byte()函數用于将字元串中的半角字元轉換為全角字元，而to_single_byte()函數則用來将字元串中的全角字元轉換為半角字元。例子如下：

db2

db2中沒有提供專門進行顯式類型轉換的函數，取而代之的是借用了很多進階語言中的強制類型轉換的概念，也就是使用目标類型名做為函數名來進行類型轉換，比如要将expr轉換為日期類型，那麼使用date(expr)即可。這種實作機制非常友善，降低了學習難度。

下面的sql語句展示了db2中類型轉換的方式：

空值處理

在資料庫中經常需要對空值（null）做處理，比如“如果名稱為空值則傳回别名”，甚至還有更複雜的需求，比如“如果名稱為空值則傳回别名，如果别名也為空則傳回‘佚名’兩個字”、“如果名稱為與别名相等則傳回空值，否則傳回名稱”。這些需求已經帶有流程控制的色彩了，一般來說需要在宿主語言中使用流程控制語句來進行處理，可是如果是在報表程式等大資料量的程式中把這些任務交給宿主語言的話會大大降低運作速度，是以我們必須想辦法在sql這一層進行處理。

為了更好的示範本節中的例子，我們需要對t_person表中的資料進行一下修改，也就是将kerry的出生日期修改為空值，将smith的出生日期和注冊日期都修改為空值，執行下面的sql語句：

coalesce()函數

主流資料庫系統都支援coalesce()函數，這個函數主要用來進行空值處理，其參數格式如下：

coalesce()函數的第一個參數expression為待檢測的表達式，而其後的參數個數不定。

coalesce()函數将會傳回包括expression在内的所有參數中的第一個非空表達式。如果expression不為空值則傳回expression；否則判斷value1是否是空值，如果value1不為空值則傳回value1；否則判斷value2是否是空值，如果value2不為空值則傳回value3；……以此類推，如果所有的表達式都為空值，則傳回null。

我們将使用coalesce()函數完成下面的功能，傳回人員的“重要日期”：如果出生日期不為空則将出生日期做為“重要日期”，如果出生日期為空則判斷注冊日期是否為空，如果注冊日期不為空則将注冊日期做為“重要日期”，如果注冊日期也為空則将“2008年8月8

日”做為“重要日期”。實作此功能的sql語句如下：

這裡邊最關鍵的就是kerry和smith這兩行，可以看到這裡的計算邏輯是完全符合我們的需求的。

nullif()函數

主流資料庫都支援nullif()函數，這個函數的參數格式如下：

如果兩個表達式不等價，則nullif傳回第一個expression1的值。如果兩個表達式等價，則nullif傳回第一個expression1類型的空值。也就是傳回類型與第一個expression相同。

下面的sql示範了nullif()函數的用法：

case函數

coalesce()函數隻能用來進行空值的邏輯判斷處理，如果要實作“如果年齡大于25則傳回姓名，否則傳回别名”這樣的邏輯判斷就比較麻煩了。在主流資料庫系統中提供了case函數的支援，嚴格意義上來講case函數已經是流程控制語句了，不是簡單意義上的函數，不過為了友善，很多人都将case稱作“流程控制函數”。

case函數有兩種用法，下面分别介紹。

用法一

case函數的文法如下：

case函數對表達式expression進行測試，如果expression等于value1則傳回returnvalue1，如果expression等于value2則傳回returnvalue2，expression等于value3則傳回returnvalue3，…… 以此類推，如果不符合所有的when條件，則傳回預設值defaultreturnvalue。

可見case函數和普通程式設計語言中的switch……case語句非常類似。使用case函數我們可以實作非常複雜的業務邏輯。下面的sql用于判斷誰是“好孩子”，我們比較偏愛tom和lily，是以我們将他們認為是好孩子，而我們比較不喜歡sam和kerry，是以認為他們是壞孩子，其他孩子則為普通孩子：

case函數在制作報表的時候非常有用。比如表t_customer中的flevel字段是整數類型，它記錄了客戶的級别，如果為1則是vip客戶，如果為2則是進階客戶，如果為3則是普通客戶，在制作報表的時候顯然不應該把1、2、3這樣的數字顯示到報表中，而應該顯示相應的文字，這裡就可以使用case函數進行處理，sql語句如下：

用法二

上邊一節中介紹的case語句的用法隻能用來實作簡單的“等于”邏輯的判斷，要實作“如果年齡小于18則傳回‘未成年人’，否則傳回‘成年人’”是無法完成的。值得慶幸的是，case函數還提供了第二種用法，其文法如下：

其中的condition1 、condition 2、condition 3……為條件表達式，case函數對各個表達式從前向後進行測試，如果條件condition1為真則傳回returnvalue1，否則如果條件condition2為真則傳回returnvalue2，否則如果條件condition3為真則傳回returnvalue3，……以此類推，如果不符合所有的when條件，則傳回預設值defaultreturnvalue。

這種用法中沒有限制隻能對一個表達式進行判斷，是以使用起來更加靈活。比如下面的sql語句用來判斷一個人的體重是否正常，如果體重小于40則認為太瘦，而如果體重大于50則認為太胖，介于40和50之間則認為是正常：

[ 尐魚兒的qq群：726994578 ] --- [ https://github.com/godmaybelieve ]