IsNull and Coalesce

ISNULL ：以指定的取代值來取代 NULL。
COALESCE ：傳回其引數中第一個非 Null 的運算式。

ISNULL 是用來判斷欄位的值，若為 NULL 的時候，可以回傳自己所要的值。 COALESCE 就像是 CASE WHEN 陳述式，會回傳引數中第一個非 Null 的運算式，如果所有引數都是 NULL， COALESCE 便會傳回 NULL。

ISNULL

declare @T table (id char(2), age decimal(3,1));
insert @T values ('01', 10.0);
insert @T values ('02', null);
insert @T values ('03', 15.0);

--總計函式會忽略Null欄位
select AVG(age) from @T             -- return 12.5

--若為 NULL 回傳 11
select AVG(isnull(age,11)) from @T  -- return 12

COALESCE

Coalesce ：回傳引數中第一個非 Null 的運算式

declare @T table ( productid varchar(2), price1 int, price2 int, price3 int);

insert @T values ('01',10,11,12);
insert @T values ('02',null,11,12);
insert @T values ('03',null,null,12);
insert @T values ('04',10,null,12);
insert @T values ('05',null,null,null);

select productid , Coalesce(price1, price2, price3) from @T
select productid , Coalesce(price1, price2, price3, 15) from @T

日期時間函式

取得系統和日期時間

SELECT GetDate()			--datetime 
SELECT SysDateTime()		--datetime2
SELECT SysUtcDateTime()		--datetime2
SELECT SysDateTimeOFFSET()	--datetimeoffset
SELECT ToDateTimeOffset(SysDateTimeOFFSET(), '+02:00')

較高精確度的日期時間

函式名稱	說明	備註
SYSDATETIME	取得主機上的時間	傳回 datetime2(7) 型別
SYSDATETIMEOFFSET	取得主機上的時間	傳回 datetimeoffset(7) 型別
SYSUTCDATETIME	取得即時的 UTC 時間	傳回 datetime2(7) 型別

SELECT 'SysDateTime()', SysDateTime()

SELECT 'SysDateTimeOFFSET()', SysDateTimeOFFSET()

SELECT 'SysUtcDateTime()', SysUtcDateTime()

較低精確度的日期時間

函式名稱	說明	備註
GETDATE	取得主機上的時間	傳回 datetime 型別
GETUTCDATE	取得即時的 UTC 時間	傳回 datetime 型別
CURRENT_TIMESTAMP	取得主機上的時間	同 GetDate() ,但這個不是參數型函式

SELECT 'GetDate()', GetDate()

SELECT 'GetUTCDate()', GetUTCDate()

SELECT 'CURRENT_TimeSTAMP', CURRENT_TimeSTAMP  -- 同 GetDate() ,但這個不是參數型函式

分解日期時間中的各個部份

DATEPART ：取得日期時間欄位指定部份的值，回傳整數型別
DATENAME ：取得日期時間欄位指定部份的值，回傳字串型別
DAY ：
MONTH ：
YEAR ：

DATEPART 和 DateName 用法相同，只是回傳型態不同。它們都帶有一個 datepart 參數，這個參數可使用的值如下例：

SELECT DateName(YY,@Now) YY,    -- year
	   DateName(MM,@Now) MM,    -- month
	   DateName(DD,@Now) DD,    -- day
	   DateName(HH,@Now) HH,    -- hour
	   DateName(MI,@Now) MI,    -- minute
	   DateName(SS,@Now) SS,    -- second
	   DateName(MS,@Now) MS     -- millisecond
	   
SELECT DatePart(QQ,@Now) QQ,    -- quarter 本年度第幾季
	   DatePart(WK,@Now) WK,    -- week 本年度第幾週
	   DatePart(DY,@Now) DY,    -- dayofyear 本年度第幾天
	   DatePart(mcs,@Now) mcs,  -- microsecond
	   DatePart(ns,@Now) ns     -- nanosecond
	   
SELECT Year(@Now) YEAR, 
       Month(@Now) MONTH, 
       Day(@Now) DAY

日期時間的運算

常用的時間運算函式有：

DATEDIFF ：Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
DATEADD ：Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

DATEADD (datepart , number , date )
    DATEDIFF ( datepart , startdate , enddate )

PS. 雖然在 DATENAME 函式中，若 datepart=dy ，是表示今年的天數，但是在 DATEDIFF 函式中是表示二個日期相差的天數， quarter和 week 也是如此。

DECLARE @Time1 DateTime = '2012-05-10 11:35:41';
DECLARE @Time2 DateTime = GetDate();
SELECT DateDiff(YY, @Time1, @Time2) Diff_YY, 
       DateDiff(MM, @Time1, @Time2) Diff_MM, 
	   DateDiff(DD, @Time1, @Time2) Diff_DD,
	   DateDiff(HH, @Time1, @Time2) Diff_DD,
	   DateDiff(MI, @Time1, @Time2) Diff_MI,
	   DateDiff(SS, @Time1, @Time2) Diff_SS,
	   DateDiff(MS, @Time1, @Time2) Diff_MS

SELECT DATEADD(YY, 1, @Time1),
	   DATEADD(QQ, 1, @Time1),
	   DATEADD(MM, 1, @Time1)
	   DATEADD(HH, 1, @Time1)

新版的 SQL 還增加了底下幾個函式：

EOMONTH ：傳回指定日期的當月最後一天，回傳型別為 date。(SQL2012)
SWITCHOFFSET ：依指定時間變換時間。(SQL2008)
TODATETIMEOFFSET ：變更某個時間的時區值。(SQL2008)

--取得當月最後一天的日期
DECLARE @date DateTime = '2012-03-10 06:05:003'; 
SELECT EOMONTH ( @date, -1 )  AS 'Last Date in Previous Month';		-- 2012-02-29
SELECT EOMONTH ( @date )	  AS 'Last Date in This Month'			-- 2012-03-31
SELECT EOMONTH ( @date, 1 )   AS 'Last Date in Next Month';			-- 2012-04-30

DECLARE @Time2 datetimeoffset = '2012-05-10 06:05:03.00000 +8:00';

--將 Time2 由 +8:00 時區的時間轉換成 -04:00 時區的時間
SELECT SWITCHOFFSET(@Time2, '-04:00')	
-- Return: 2012-05-09 18:05:03.0000000 -04:00

--將 Time2 的時區值變更成 -04:00 時區值
SELECT ToDateTimeOffset(@Time2, '-04:00')	
-- Return: 2012-05-10 06:05:03.0000000 -04:00

建立 DateTime、Date、Time

以下６個函式都是 SQL2012 才有的支援：

函式名稱	說明	備註
TIMEFROMPARTS	建立時間	傳回 time 型別
DATEFROMPARTS	建立日期	傳回 date 型別
SMALLDATETIMEFROMPARTS	建立日期時間	傳回 smalldatetime 型別
DATETIMEFROMPARTS	建立日期時間	傳回 datetime 型別
DATETIME2FROMPARTS	建立日期時間	傳回 datetime2 型別
DATETIMEOFFSETFROMPARTS	建立日期時間	傳回 datetimeoffset 型別

TIMEFROMPARTS           ( hour, minute, seconds, fractions, precision )
DATEFROMPARTS           ( year, month, day )
SMALLDATETIMEFROMPARTS  ( year, month, day, hour, minute )
DATETIMEFROMPARTS       ( year, month, day, hour, minute, seconds, milliseconds )
DATETIME2FROMPARTS      ( year, month, day, hour, minute, seconds, fractions, precision )
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

其他與日期時間相關的函式

SET DATEFIRST

SELECT @@DATEFIRST

SET DATEFIRST 1

SET DATEFORMAT

設定系統如何解讀日期字串之年月日的順序。

格式有效的參數為： mdy、dmy、ymd、ydm、myd 和 dym 。

SET DATEFORMAT dmy;
DECLARE @date1 datetime = '31/12/2012 09:01:01';
SELECT @date1

SET DATEFORMAT ymd;
DECLARE @date2 datetime = '2012/12/31 09:01:01';
SELECT @date2

SET LANGUAGE

設定工作階段和系統訊息的語言環境

select @@LANGUAGE 

SET LANGUAGE 繁體中文
SET LANGUAGE us_english

sp_helplanguage

字串函式

SQL2012 新功能

FORMAT ：將數值或日期資料，轉換成指定格式的字串。(SQL2012)
CONCAT ：傳回串連兩個以上之字串值的結果字串。

FORMAT

FORMAT 是用來將 date/time 或 number 型別的資料，轉換成具有文化特性格式的字串值。

日期類型的Format

傳回不同文化特性的日期格式

DECLARE @d DATETIME = '2011/12/31';

SELECT FORMAT ( @d, 'D', 'en-US' ) AS '美式'
      ,FORMAT ( @d, 'D', 'en-gb' ) AS '英式'
      ,FORMAT ( @d, 'D', 'de-de' ) AS '德式'
      ,FORMAT ( @d, 'D', 'zh-tw' ) AS '台式'; 

SELECT FORMAT ( @d, 'd', 'en-US' ) AS '美式(簡)'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS '英式(簡)'
      ,FORMAT ( @d, 'd', 'de-de' ) AS '德式(簡)'
      ,FORMAT ( @d, 'd', 'zh-tw' ) AS '台式(簡)';

數值類型的Format

傳回不同格式的數值

declare @i money = 1.5491

SELECT @i as 'Original Format'
    ,FORMAT(@i, 'N', 'en-us') AS 'Number Format'
    ,FORMAT(@i, 'G', 'en-us') AS 'General Format'
    ,FORMAT(@i, 'C', 'en-us') AS 'Currency Format'

傳回自訂格式

SELECT 
	FORMAT(223939889,'(0#)-####-####') AS 'Phone Format',
	FORMAT(952123456,'0###-######') AS 'Mobil Format';

子字串和位置

SUBSTRING ：
LEFT ：
RIGHT ：
LTRIM ：
RTRIM ：
REPLACE ：取代子字串
STUFF ：將指定字串插入另一個字串
CHARINDEX ：指定字串，找出首次出現的位置
PATINDEX ：依指定字串樣本，找出首次出現的位置（字串樣式支援萬用字）
REPLICATE ：重複指定字串
SPACE ：重複空白字串

declare @tmp varchar(50) = ' abcd bc d32ef ';

SELECT 
	SUBSTRING(@tmp, 1,3),	--abc
	LEFT(@tmp, 3),			--ab
	RIGHT(@tmp, 3),			--ef
	RTRIM(LTRIM(@tmp))		--abcd bc d32ef
	
SELECT 
	CHARINDEX('bc', @tmp),		--2
	CHARINDEX('g', @tmp),		--0
	PATINDEX('%bc%', @tmp),		--2
	PATINDEX('%[0-9]%', @tmp)	--10

SELECT 
    REPLICATE('abc',3),					--abcabcabc
    'a' + SPACE(2) + 'b',				--a  b
    REPLACE('abc123abc','123','xyz')	--abcxyzabc

DECLARE @d DATETIME = GETDATE();
SELECT 
	FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'DateTime Result',   --27/09/2012       
	FORMAT(123456789,'###-##-####') AS 'Custom Number Result';  --123-45-6789

字串變換

CONCAT ：合併
LOWER ：轉大寫
UPPER ：轉小寫
REVERSE ：反轉
STR ：將數值轉字串

-- 將 123.45 轉成六個位數的字元字串
-- 數字的小數點後部份會捨入到一個小數位數。
SELECT STR(123.45, 6, 1);	-- 123.5

字串長度

LEN ：回傳字數 (number of characters)
DATALENGTH ：回傳字元數 (number of bytes)

SELECT 
LEN('ABC'),LEN('ＡＢＣ'),				--3,3
DATALENGTH('ABC'),DATALENGTH('ＡＢＣ')	--3,6

如何在字串中反向找到特定字元的位置

使用 CharIndex 可以找到特定字元在字串中首次出現的位置，如果要由後面開始算呢？目前 SQL 內建函式好像沒有直接這樣的功能，不過可以利用以上的方法來取得。假設有個字串「abc.def.xyz」，你要取得最後一個「.」的位置，你可以這麼做：

declare @temp varchar(100) = 'abc.def.xyz'

select LEN(@temp) - CHARINDEX('.', REVERSE(@temp))

數學函式

算術函式

ROUND ：捨入到指定的長度或有效位數。 (指定有效位數，以下四捨五入)
FLOOR ：傳回小於或等於指定數值運算式的最大整數。 (取整數，無條件捨去小數)
CEILING ：傳回大於或等於指定數值運算式的最小整數。 (取整數，小數無條件進位)
ABS ：絕對值
SQRT ：求平方根
SQUARE ：求平方數
SIGN ：判斷正或負，負數值回傳-1，零值回傳0，正數值回傳1。
POWER ：傳回指定乘冪之指定運算式的值，例 Power(2,3) = 2³。
PI ：傳回 PI 的常數值

若要求除法後的整數和餘數，沒有函式，可以直接使用 / 和 % 運算

select 11/3     -return 3
select 11 % 3   -return 2

ROUND

對數值欄位指定有效位數，以進行四捨五入計算

ROUND ( numeric_expression , length [ ,function ] )   //length : 取到小數第幾位，若為負值，表示取到整數第幾位

範例１：

select ROUND(123.9994, 3)	--123.9990 
select ROUND(123.9995, 3)	--124.0000 
select ROUND(123.4545, 2)	--123.4500 
select ROUND(123.4545, -2)	--100.0000
select ROUND(748.58, -1)	--750.00 
select ROUND(748.58, -2)	--700.00 
select ROUND(748.58, -3)	--1000.00 執行批次時發生錯誤。錯誤訊息為: 算術溢位。
select ROUND(748.58, -4)	--0

範例２：

假設資料庫中以下數字 {10.5, 12, 3.7, 1, 3, 9, 13.5}，型別為NUMBER(5,2)，要如何找出帶小數的值 {10.5, 3.7, 13.5}？
1.轉字串後，再判斷字串含不含 dot
2.執行 mod 1 後，判斷含不含餘數
3.使用 ROUND ＝＞ (VALUE - ROUND(VALUE,0)) <> 0

三角或指數函式

SIN ：三角正弦函式 (Sine)
COS ：三角餘弦函式 (CoSine)
TAN ：三角正切函式 (Tangent)
COT ：三角餘切函式 (CoTangent)
ASIN ：反正弦函式 (ArcSine)
ACOS ：反餘弦函式 (ArcCosine)
ATAN ：反正切函式 (ArcTangent)。
DEGREES ：以弧度換算對應的角度(θ)
RADIANS ：以角度換算對應的弧度(rad)
LOG10 ：以 10 為基底的對數。
LOG ：自然對數，以 e 為基底的對數，其中 e 是大約等於 2.718281828 的無理常數。Log(10) ≒ 2.3
EXP ：自然指數，以 e 為基底的指數。Exp(3) = e³ ≒ 20.0855

角度與弧度

亂數函式

RAND ：傳回 0 到 1 (不含) 的虛擬隨機(pseudo-random) float 值。

邏輯函數

IsNull

ISNULL ( para1 , para2 )

若 para1 為 NULL，則回傳 para2

DECLARE @x int = 10, @y int = null
SELECT ISNULL ( @x , 0 )        --10
SELECT ISNULL ( @y , 0 )        --0

NullIf

NULLIF ( expression1 , expression2 )

NULLIF 接受二個表示式，若 exp1=exp2 則回傳 NULL，否則回傳 exp1 。

declare @a int;
declare @b int;
set @a=2; 
set @b=2;
select NULLIF(@a, @b)	--NULL

set @a=2; 
set @b=3;
select NULLIF(@a, @b)	--2

IIF

IIF 函式為 SQL2010 新功能，判斷運算式的真偽。

IIF ( boolean_expression, true_value, false_value )

true_value 及 false_value 可以是任何類型。
true_value 及 false_value 也可以是 null ，但是不能同時都為 null 。
若 boolean_expression 錯誤，則函式會中斷。

DECLARE @a int = 45, @b int = 30;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;              -- return TRUE
SELECT IIF ( @a > @b, 1, 0 ) AS Result;                         -- return 1

DECLARE @x int = 45, @y varchar(2) = '0';
SELECT IIF ( @x > @y, 'TRUE', 'FALSE' ) AS Result;              -- return TRUE
SELECT IIF ( @x / @y > 0 , 'TRUE', 'FALSE' ) AS Result;         -- error

CHOOSE

CHOOSE 函式為 SQL2010 新功能，從數值清單傳回指定之索引的項目。

CHOOSE ( index, val_1, val_2 [, val_n ] )

SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result;
--return: Developer

SELECT Gender, CHOOSE ( Gender, '男', '女') as Expression1 FROM Employees
Gender   Expression1
-------- ----
  1      男
  2      女

CASE ... WHEN

CASE ... WHEN有二種使用方式

simple form

這個格式比較簡單，指定固定欄位當做比較式的來源。

SELECT ProductID, ProductName,
	CASE Discontinued
		WHEN 0 THEN 'No'
		WHEN 1 THEN 'Yes'
		ELSE 'Unknow'
	END as Discontinued
FROM Products

searched form

這個格式比較彈性，因為比較式不限定同一個欄位。

SELECT ProductID, ProductName,
	CASE 
		WHEN Discontinued=0 THEN 'No'
		WHEN Discontinued=1 THEN 'Yes'
		ELSE 'Unknow'
	END as Discontinued
FROM Products

系統函式

以下幾個也是常用的內建函式或是系統變數：

型別轉換函式

CAST ：轉換運算式的資料類型。
CONVERT ：轉換運算式的資料類型。

CAST ( expression as data_type )
CONVERT ( data_type , expression [ , style ] )

底下為幾個較常用的 style ：

style		輸出格式	說明
２碼	４碼	輸出格式	說明
	0或100	mon dd yyyy hh:miAM(PM)	預設
1	101	mm/dd/yyyy	美式
2	102	yyyy.mm.dd	ANSI
3	103	dd/mm/yyyy	英式
8	108	hh:mi:ss
11	111	yyyy/mm/dd	日式
12	112	yyyymmdd	ISO

DECLARE @Time1 DateTime = '2012-05-10 06:05:003';

SELECT 
CAST( @Time1 as varchar(10) ),	--05 10 2012
CAST( @Time1 as varchar(15) ),	--05 10 2012  6:0
CAST( @Time1 as varchar(18) )	--05 10 2012  6:05AM

SELECT 
CONVERT( varchar(18), @Time1, 0 ),		--05 10 2012  6:05AM
CONVERT( varchar(18), @Time1, 101 ),	--05/10/2012
CONVERT( varchar(18), @Time1, 102 ),	--2012.05.10
CONVERT( varchar(18), @Time1, 103 ),	--10/05/2012
CONVERT( varchar(18), @Time1, 108 ),	--06:05:03
CONVERT( varchar(18), @Time1, 111 ),	--2012/05/10
CONVERT( varchar(18), @Time1, 112 )		--20120510

執行結果回應函式

@@ERROR ：傳回最後執行之 Transact-SQL 陳述式的錯誤號碼。
@@TRANCOUNT ：Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.
@@ROWCOUNT ：傳回受到前一個陳述式所影響的資料列數。

中繼資料函式（Metadata Functions）

OBJECT_ID ：
OBJECT_NAME ：
DB_ID ：
DB_NAME ：
COL_NAME ：

SELECT DB_ID('test')		--19
SELECT DB_NAME(19)			--test

SELECT OBJECT_ID('tblUser')		--713821655
SELECT OBJECT_NAME(713821655)	--tblUser

--取得tblUser的所有欄位名稱
SELECT * FROM sys.columns  where OBJECT_ID = 713821655

--由 tableid+columnid 取得欄位名稱
SELECT COL_NAME (713821655,1)	--UserID

目錄檢視（Catalog Views ）

下列內建的檢視（View）可以用來查詢各類別的物件資訊。

sys.objects ：所有物件
sys.tables ：取得資料表物件（sys.objects.type = U）
sys.columns ：取得資料行物件
sys.triggers ：取得觸發程序物件
sys.views ：取得檢視物件（sys.objects.type = V）
sys.indexes ：取得資料表、檢視或資料表值函式的索引
sys.foreign_keys ：取得 FOREIGN KEY 條件約束（sys.object.type = F）
sys.procedures ：取得程序物件（sys.object.type = P、X、RF、PC）

2013年5月8日 星期三

使用內建函式查詢