昨天已經將大部分常用的 SQL 指令做一個簡單的說明，接下來我們接著聊一聊由資料庫系統所提供的系統函數。這些系統函數在我們寫 Store Procedure 時，提供很多強大的功能，方便我們整合到我們的 SQL 程式中使用。不同的資料庫都會有自己的一套系統函數，我之前有整理過常用系統函數的比較一覽表，請參考下面的列表說明。
| 函式名 | MS-SQL | MySQL | PostgreSQL |
|---|---|---|---|
| 聚合函數 | |||
| 取某欄位的平均值 | avg() | avg(col) | avg(expression) |
| 計算筆數 | count() | count(col) | count(xx) |
| 某個欄位值最大值 | max() | max(col) | max(expression) |
| 某個欄位值的最小值 | min() | min(col) | min(expression) |
| 某個欄位取值的總和 | sum() | sum(col) | sum(expression) |
| 字串類 | |||
| 取ascii碼 | ascii(char) | ascii(char) | ascii(string) |
| 根據ascii碼取字元 | char(ascii) | chr() | chr(int) |
| 轉化為指定的資料類型 | cast() | cast() | to_char() |
| 將s1,s2...,sn連接成字串 | + | concat(s1,s2...,sn) | concat(x,y,x) |
| 返回字串的位元長度 | datalength(Char_expr) | data_length(str) | char_length(string) |
| 返回字串長度 | len() | length(s) | length(string) |
| 取子字串 | substring(exp,start,len) | substring() | substring() |
| 返回字串右邊 x 個字元 | right(str,x) | right(str,x) | right(str text, n int) |
| 返回字串左邊 x 個字元 | left(str,x) | left(str,x) | left(str text, n int) |
| 轉爲大寫 | upper(str) | upper(str) | upper(string) |
| 轉爲小寫 | lower(str) | lower(str) | lower(string) |
| 生成n個空格 | space(n) | ||
| 複制字串n 次 | replicate(str,n) | repeat(str,src,rplcstr) | repeat() |
| 反轉字串 | reverse(str) | reverse(str) | reverse(str) |
| 字串代替 | replace() | replace() | replace() |
| 指定位置插入指定字串 | stuff(str,x,y,instr) | insert(str,x,y,instr) | |
| 取掉左方空格 | ltrim(char_expr) | ltrim(str) | ltrim(str) |
| 取掉右方空格 | rtrim(char_expr) | rtrim(str) | rtrim(str) |
| 返回str在sub的起始位置 | charindex(substr,str) | position(substr,str) | strpos(str, substr) |
| 條件式判斷 | iif(bool,val_t,val_f) | iif(bool,val_t,val_f) | |
| 若 null取第2個引數 | isnull(bool,val_null) | ifnull() | |
| 反斜線轉義str中的單引號 | quote(str) | ||
| 去除字串前後的所有空格 | trim(str) | trim(str) | |
| 日期格式化 | convert() | date_format(date,fmt) | to_date(text, text) |
| 比較字串s1和s2 | strcmp(s1,s2) | ||
| 固定字串長度(左或右) | lpad or rpad | lpad or rpad | |
| 數學類 | |||
| 求num絕對值 | abs(num) | abs(x) | abs(x) |
| 取指數 | exp(float_expr) | exp(x) | exp(x) |
| 返回x/y的模（餘數） | mod() | mod(x,y) | mod(y, x) |
| 隨機數産生器 | rand([int_expr]) | rand() | random() |
| 四捨五入 | round(x,y) | round(x,y) | round(x,y) |
| 平方根 | sqrt(float_expr) | sqrt(x) | sqrt(x) |
| 返回數字x截短為y位小數 | truncate(x,y) | trunc(x,y) | |
| 日期類 | |||
| 返回當前日期及時間 | now() | Now() | now() |
| 返回日期 | getdate() | current_date() | current_date |
| 返回當前的時間 | current_time() | current_time | |
| 返回日期加上 number | dateadd(type,int,d_exp) | date_add()/date_sub() | date_part(text, times) |
| 日期差 | datediff(type,d1,d2) | datediff(date1,date2) | |
| 傳回日期欄的西元年 | year(date) | year(date) | |
| 傳回日期欄的月 | month(date) | dayofmonth(date) | |
| 傳回日期欄的日 | day(date) | dayname(date) | |
| 傳回日期欄是星期幾 | dayofweek(date) | ||
| 返回日期為一年中第幾 | week(date) | ||
| 系統函數 | |||
| 數據庫名 | db_name() | database() | current_database() |
| 伺服器的版本 | version() | version() |
善用上面這些系統的函數，可以大幅提升我們撰寫SQL的效率。那如果找不到所需的函數時該怎麼辦? 當然就只能自己寫了，限於篇幅我就不在此繼續說明。在接下來的進階說明，如果有機會，我們再來深入聊聊。
也就是因為資料庫允許自行開發所需的函數，讓SQL 更接近一種程式語言，如果想要深入的運用資料庫，就應該要下功夫努力學習，今天就聊到這邊，我們明天繼續努力。
iThome鐵人賽
看影片追技術