Sql Server 常用函數

標籤：

中繼資料函數

DB_ID
擷取資料庫唯一標識，是伺服器上的唯一標識
文法結構 ：DB_ID([‘database_name‘])
參數說明 ：database_name是資料庫名稱，為選擇性參數。如果沒有指定則返回當前所在資料庫的ID
傳回值：int類型的資料庫標識符
使用：SELECT DB_ID();

DB_NAME
擷取當前資料庫的名稱
文法結構 ：DB_NAME([‘database_id‘])
參數說明 ：database_id為選擇性參數，是資料庫的id，可由DB_ID函數獲得、如果沒有指定則返回當前所在資料庫的名稱
傳回值 ：資料庫名稱
使用 ：SELECT DB_NAME();

OBJECT_ID
擷取資料庫物件標識符，是資料庫中的唯一標識
文法結構 ：OBJECT_ID(‘[database_name.[schema_name].|schema_name .]object_name‘[,‘object_type‘])
參數說明：

名稱	說明	是否可選
database_name	資料庫名稱	可選
schema_name	架構名稱	可選
object_name	對象名稱	必選
object_type	物件類型	可選

傳回值：int類型的物件識別碼
使用：
IF OBJECT_ID(N‘dbo.User‘,N‘U‘) IS NOT NULL
DROP TABLE dbo.User;
GO

OBJECT_NAME
擷取資料庫物件名稱
文法結構 ：OBJECT_NAME(object_id)
參數說明 ：object_id是資料庫物件的id，可用OBJECT_ID獲得
傳回值 ：資料庫物件名稱

OBJECTPROPERTY
獲得指定資料庫、指定對象的指定屬性值。
文法結構 ：OBJECTPROPERTY(id,property)
參數說明：
id表示為指定的資料庫、對象的id
property表示要返回的屬性

常見的property屬性值

屬性值	說明	傳回值
TableHasTextImage	表中是否含有text、image資料類型的列	int   1表示true 0表示false
TableHasPrimaryKey	表中是否含有主鍵	int   1表示true 0表示false
TableHasIndex	表中是否含有索引	int   1表示true 0表示false
TableHasForeignKey	表中是否含有外鍵	int   1表示true 0表示false
SchemaId	對象的架構Id	int
OwnerId	對象所有者	int
IsView	是否是視圖	int   1表示true 0表示false
IsUserTable	是否是使用者建立的表	int   1表示true 0表示false
IsTable	是否是表	int   1表示true 0表示false
IsSystemTable	是否是系統資料表	int   1表示true 0表示false
IsPrimaryKey	是否是主鍵	int   1表示true 0表示false

彙總函式

AVG
計算映射集平均值，如果參數值為NULL則這一行會被忽略計算
文法結構 :AVG([all | distinct] expression)
參數說明 :
all:為預設值，標識對所有的資料都計算平均值
distinct：去重，相同值僅作為一次計算
expression: 運算式（列）。運算式內部不允許使用子查詢和其他彙總函式。
使用：select ave(age) from student

MIN
計算映射集最小值，可用於numeric、char、varchar或datetime、money或smallmoney列，但不能用於bit列，忽略null值
文法結構 ：MIN([all | distinct] expression)
參數說明 ：同AVG
使用：select min(avg) from student

MAX
計算映射集中最大值。和MIN函數一樣，可用於numeric、char、varchar或datetime、money或smallmoney列，但不能用於bit列，忽略null值
文法結構：MAX([all | distinct] expression)
參數說明：同AVG
使用：select max(age) from student

SUM
用於求和，只能用於數字類型（bit類型除外），忽略null值
文法結構：SUM([all | distinct] expression)
參數說明：同AVG
使用：select sum(grade) from class

COUNT
用於計算滿足條件的資料項目的個數
文法結構：COUNT([[all | distinct] expression] | *)
參數說明：expression是除text、image或ntext以外任何的運算式，但不允許是彙總函式和子查詢
使用：
count(*) 返回所有的列項數，包括含NULL和重複項的列
count(all expression) 返回非NULL值的列的個數
count(distinct expression) 返回唯一非NULL值的列的個數

STDEV
計算標準差
文法結構：STDEV([all | distinct] expression)
參數說明：expression必須是一個數實值型別的運算式（不包括bit類型），不允許使用彙總函式和子查詢，忽略null值
使用：select stdev(age) from student

VAR
計算方差
文法結構：VAR([all | distinct] expression)
參數說明：同STDEV
使用：select var(age) from student

數學函數
ABS
計算絕對值
文法結構：ABS(expression)
參數說明：expression必須是一個數實值型別的運算式（bit類型除外）
使用：select abs(-1)

CELLING
擷取大於等於最小整數值。俗稱天花板函數，盡量往最大的取整。
文法結構：CELLING(expression)
參數說明：同ABS
使用：select celling(15.2) --輸出16

FLOOR
擷取小於等於最大整數值。與CELLING函數相對，俗稱地板函數，盡量往最小的取整
文法結構：FLOOR(expression)
參數說明：同ABS
使用：select floor(15.6) --輸出15

ROUND
擷取指定長度和精度的數值
文法結構：ROUND(expression,length[, function])
參數說明：
expression 數值運算式
length 舍入的精度。正數-保留小數點後幾位；負數保留小數點前幾位
function 截斷小數。值為tinyint、smallint或int。值省略或為0則進行舍入。如果指定了0以為的值則截斷小數
使用：
select round(123.666,0) --124.00
select round(123.66,0,1) --123.00

RAND
返回0到1之間的浮點數
文法結構： RAND([seed])
參數說明：seed是種子值，是一個整數類型（tinyint、smallint、int）的運算式。
使用：select rand()

字串函數

ASCII
擷取指定字元的ASCII編碼
文法結構：ASCII(expression)
參數說明：expression是一個char或varchar類型的運算式。
使用：select ascii(‘k’) --107

CHAR
ASCII函數的逆操作，通過ASCII碼獲得相應的字元
文法結構：CHAR(integer_expression)
參數說明：integer_expression是一個整數類型的運算式
使用：select char(107) --k

Unicode
擷取指定字元的Unicode編碼
文法結構：Unicode(expression)
參數說明：expression是一個nchar或nvarchar類型的運算式
使用： select unicode(‘k’)

NCHAR
Unicode函數的逆操作，通過Unicode碼來獲得相應的字元
文法結構：NCHAR(integer_expression)
參數說明：integer_expression是一個整數類型的運算式
使用： select nchar(107)

PatIndex
擷取字串第一次出現的位置，沒有匹配到的話則返回0
文法結構：PatIndex(‘%pattern%’,expression)
參數說明：
pattern：可以是一個字串，也可以使用萬用字元。如果使用萬用字元則需要配對
expression:字元運算式
傳回值：如果expression資料類型為varchar(max)或nvarchar(max)則為bigint，否則為int
使用：select patindex(‘%llo%’,’hello world!’)

SPACE
產生空格
文法結構：SPACE(integer_expression)
參數說明：integer_expression表示產生幾個空格
使用： select space(3)

REPLICATE
按指定的次數重複產生一個字串
文法結構：REPLICATE(character_expression,integer_expression)
參數說明：
character_expression 要產生的字串
integer_expression 產生的次數
使用： select replicate(‘hello’,5)

SUBSTRING
對字串進行截取，索引從1開始
文法結構：SUBSTRING(expression,start,length)
參數說明：
expression  可以是字串、二進位字串、文本、映像或包含列的運算式，但不能使用彙總函式的運算式
start 指定子字串開始位置
length 返回字元的個數，不能為負數
傳回值：
如果expression是受支援的字元資料類型，則返回字元資料；如果expression是受支援的binary資料類型，則返回位元據
使用： select substring(‘hello’,1,2)

LEN
擷取字串的長度，但不包括右邊的空格。
文法結構：LEN(string_expression)
參數說明：要計算長度的字串
傳回值：expression資料類型為varchar(max)、nvarchar(max)或varbinary(max)則為bigint。否則為int
使用： select LEN(‘王尼瑪’)

STUFF
在指定的字串中刪除指定長度的字串，並在起點處插入另外一組字元
文法結構：STUFF(source_character_expression,start,length,destination_character_expression)
參數說明：
source_character_expression 源字串。可以是常量、變數，也可以是字元列或位元據列
start 指定刪除和插入的開始位置。如果start或length為負數，則返回Null 字元串。如果start比源字串長，則返回Null 字元串
length 指定要刪除的字元個數。如果length比源字串長，則全部刪除
destination_character_expression 要插入的新字串。可以是常量、變數，也可以是字元列或二級制資料列
使用： select stuff(‘abcdef’,3,2,‘111’)  --ab111ef

CHARINDEX
用於在指定的字串中搜尋特定的字串，並可以指定開始搜尋的位置，返回第一次找到目標字元的位置
文法結構：CHARINDEX(expression1，expression2[,start_location])
參數說明：
expression1 要尋找的字串的運算式
expression2 指定搜尋的字串運算式，可以是字元列
start_location 搜尋的字串的搜尋位置。如果值為負數或零，則從開頭搜尋
傳回值: 如果expression2的資料類型為varchar(max)、nvarchar(max)或varbinary(max)，則為bigint。否則為int
使用： select charindex(‘wc’,’wk wr wc’,2)

QUOTENAME
產生帶有分隔字元的Unicode字串
文法結構：QUOTENAME(‘character_string’[, ‘quote_character’])
參數說明：
character_string:Unicode字串
quote_character:用作分隔字元的單字串，預設的話為“[]”
傳回值：nvarchar(258)
使用：
select quotename(‘hello’) --[hello]
select quotename(‘hello’,’|’)  --|hello|

STR
用於將浮點數轉換為字串
文法結構：STR(float_expression [,length[,decimal]])
參數說明：
float_expression 浮點數值的運算式
length 總長度。它包括小數點、符號、數字及空格。預設值為10
decimal 小數點後的位元。decimal必須小於或等於16，如果大於16則會截斷結果
傳回值：char
使用：
select str(123.987) –124 str函數會自動四捨五入
select str(123.987,6) –兩空格 +124 因為decimal沒有指定，所以自動填滿空格
select str(123.987,6,4)--123.99

LEFT
截取左邊字串，效果等同於SUBSTRING(expression,1,length)
文法結構：LEFT(character_expression,integer_expression)
參數說明：
character_expression 字串或二進位運算式，可以是常量、變數或運算式。
integer_expression 正整數，指定返回的字元數
傳回值：varchar或nvarchar
使用：select left(‘wow’,2) --wo

RIGHT
截取右邊字串，效果等同於SUBSTRING(expression,LEN(expression)-length+1,length)
文法結構：RIGHT(character_expression,integer_expression)
參數說明：同LEFT函數
傳回值：同LEFT函數
使用： select right(‘wow’,2) –w

LTRIM
用於清除左邊空白字元
文法結構：LTRIM(character_expression)
參數說明：character_expression 為字元或位元據運算式，可以使常量、變數或資料列
傳回值：varchar或nvarchar字串
使用： select ltrim(‘   123’)

RTRIM
用於清除右邊空白字元
文法結構：RTRIM(character_expression)
參數說明：同LTRIM
傳回值：同LTRIM
使用： select rtrim(‘123   ’)

LOWER
將指定字串全部轉換成小寫字元
文法結構：LOWER(character_expression)
參數說明：character_expression 為字元或位元據運算式，可以使常量、變數或資料列
傳回值：varchar或nvarchar字串
使用： select lower(‘aBc’)  --abc

UPPER
將指定字串全部轉換成大寫字元
文法結構：UPPER(character_expressiion)
參數說明：同LOWER
傳回值：同LOWER
使用：select upper(‘aBc’) –ABC

REVERSE
反轉(反序)指定字串
文法結構：REVERSE(character_expression)
參數說明：character_expression 為字元或位元據運算式，可以使常量、變數或資料列
傳回值：varchar或nvarchar字串
使用： select reverse(‘abc’) –cba

DATALENGTH
擷取指定字串的位元組數。它不僅適合字串類型資料，還適合文本（text、ntext）、位元據（varbinary、binary）和映像（image）等任意類型的資料。
文法結構：DATALENGTH(expression)
傳回值：如果expression資料類型為varchar(max)、nvarchar(max)或varbinary(max)資料類型，則返回bigint。否則返回int
使用：select datalength(‘我’) –2

SOUNDEX
返回一個由四個字元組成的代碼，用於評估兩個字串的相似性
文法結構：SOUNDEX(‘character’)
使用：
select soundex(‘abcde’) --A120
select soundex(‘abcdf’)  --A122

DIFFERENCE
返回一個整數值，用來表示兩個字元運算式的SOUNDEX值之間的差異
文法結構：DIFFERENCE(expression1,expression2)
使用：select difference(‘abc’,’abf’)

REPLACE
在指定的字串中替換指定的字元
文法結構：REPLACE(string_expression1,string_expression2,string_expression3)
參數說明：
string_expression1 要搜尋的字串運算式。可以是字元或位元據值
string_expression2 要尋找的字串。可以是字元或位元據值
string_expression3 要替換的字串。可以是字元或位元據值
使用： select replace(‘ab’,’abcdef’,’H’)

日期時間函數

GETDATE
擷取當前Sql Server伺服器的日期和時間
使用： select getdate()

GETUTCDATE
擷取當前Sql Server伺服器的UTC時間
使用： select getutcdate()

YEAR
擷取日期的年份資訊
文法結構：YEAR(date)
參數說明：date可以為time、date、smalldatetime、datetime、datetime2或datetimeoffset值的運算式
使用：select year(‘2012’)

MONTH
擷取日期的月份資訊
文法結構：MONTH(date)
參數說明：同YEAR
使用：select month(‘2012-05’)

DAY
擷取日期的天份資訊
文法結構：DAY(date)
參數說明：同YEAR
使用：select day(‘2012-05-20’)

DATEPART
返回指定日期時間的指定部分值
文法結構：DATEPART(datepart,date)
參數說明：
datepart 指定日期時間輸出格式代碼

說明	取值
返回年度資訊	Year、YYYY、YY
返回月份資訊	Month、MM、M
返回日期資訊	Day、DD、D
返回周資訊	Week、WK、WW
返回每周星期幾資訊	WeekDay、DW
返回季度資訊	Quarter、QQ、Q
返回一年中第幾天的資訊	DayOfYear、DY、Y
返回小時資訊	Hour、HH
返回分鐘資訊	Minute、MI、N
返回秒資訊	Second、SS、S
返回毫秒資訊	MillSecond、MS

傳回值：int類型日期時間格式
使用： select datepart(yy,getdate())

DATENAME
返回指定日期時間格式的字串，和DATEPART函數一樣，只不過傳回值為字元類型
使用：select datename(yy,getdate())

DATEADD
將某個日期加上一個指定整數值，得到一個新的datetime類型的資料
文法結構：DATEADD(datepart,numerical,date)
參數說明：
numerical 要相加的值，必須是整數，如果是小數則保留整數
date 為time、date、smalldatetime、datetime、datetime2或datetimeoffset值的運算式、列運算式、使用者定義的變數或字串
datepart 日期時間輸出格式代碼
使用：select dateadd(yyyy,’2012-05-06’,’2013-05-06’)

DATEDIFF
將兩個日期按照特定的時間格式相減，得到一個新的datetime類型的資料
文法結構：DATEDIFF(datepart,startdate,enddate)
參數說明：
startdate 為time、date、smalldatetime、datetime、datetime2或datetimeoffset值的運算式、列運算式、使用者定義的變數或字串
enddate  為time、date、smalldatetime、datetime、datetime2或datetimeoffset值的運算式、列運算式、使用者定義的變數或字串
datepart 日期時間輸出格式代碼
使用：select datediff(yyyy,’2012-05-06’,’2013-05-06’)

類型轉換函式

CAST 和CONVERT它們的功能是相同的，只是文法不同
CAST
文法結構：CAST(expression as date_type)
參數說明：expression 要轉換的運算式
使用：select cast(‘123’ as int)

CONVERT
文法結構：CONVERt(data_type(length),expression,style)
參數說明：
data_type(length) 資料類型
expression 要轉換的值
style 輸出的格式
使用：select convert(int,123)

Sql Server 常用函數