SQL Server 中幾個有用的特殊函數

在SQL Server 的使用過程中，發現幾個很有用，但不太常用(或細節不太清楚)的函數(預存程序)：

isnumeric,isdate，patindex，newid，collate，sp_executesql，checksum

遂記下，以備日後查詢。不敢獨享，與君共之。有用且看，無用略過。

 

1> isnumeric( expression )

-- 傳回值 1 | 0，判斷是否是數字類型。

數實值型別包括(int、bigint、smallint、tinyint、numeric、money、smallmoney、float、decimal、real)

樣本：

select * from tablename
where isnumeric(columnname)<> 1;
go

 

以上樣本使用 isnumeric 返回所有非數值的資料行。

 

2> isdate( expression )

 

-- 如果 expression 是有效 date、time 或 datetime 值，則返回 1；否則返回 0。

樣本：

if isdate('2009-05-12 10:19:41.177') = 1
    print '有效日期'
else
    print '無效的日期'

 

上面的樣本使用 isdate 測試某一字串是否是有效 datetime。

 

3> patindex( '%pattern%' , expression )

-- 返回指定運算式中某模式第一次出現的起始位置；

-- 如果在全部有效文本和字元資料類型中沒有找到該模式，則返回零。

'pattern' ： 一個萬用字元字串。pattern 之前和之後必須有 % 字元（搜尋第一個或最後一個字元時除外）。 expression ： 通常為要在其中搜尋指定模式的字串資料型別列。

樣本：

select patindex('%BB%','AA_BB_CC_DD_AA_BB_CC_DD')
-- 返回：4

上面樣本返回的是第一個‘BB’的開始位置。

其實，使用 charindex 函數也能實現上面樣本的查詢，如下：

select charindex('BB','AA_BB_CC_DD_AA_BB_CC_DD')
--返回：4

 

 

patindex 函數與 charindex 函數的區別：select patindex('%[0-9][A-Z]%', 'AA_BB_9C_DD_AA_9F_CC_DD')
-- 返回：7 
select charindex('%[0-9][A-Z]%','AA_BB_9C_DD_AA_9F_CC_DD')
-- 返回：0

看出來沒有？patindex 函數可以使用萬用字元，而charindex 函數不能。也就是說：patindex 函數功能更強大！

 

 

4> newid( )

 

-- 建立 uniqueidentifier 類型的唯一值。這個函數總是能返回一個新的GUID號碼，它永遠不會重複，而且毫無規律。

樣本：

declare @myid uniqueidentifier
set @myid = newid()
print '@myid 的值是: '+ convert(varchar(255), @myid)

-- @myid 的值是: 0B939411-4827-485E-884B-5BEB1699CFEE

 

5> collate

 

-- 一個子句，可應用於資料庫定義或列定義以定義定序，或應用於字串運算式以應用定序轉換。collate 子句只能應用於 char、varchar、text、nchar、nvarchar 和 ntext 資料類型。

樣本：

drop table #tempTalbe
go
create table #tempTalbe
(
   _id    int,
   _name  varchar(30)
)
go
insert into #tempTalbe values(1,'中');
insert into #tempTalbe values(2,'國');
insert into #tempTalbe values(3,'人');

select * from #tempTalbe
order by _name
collate latin1_general_cs_as_ks_ws asc;
go
/* 顯示結果：
_id         _name
----------- ------------------------------
1           中
2           國
3           人
*/

select * from #tempTalbe 
order by _name 
collate Chinese_PRC_CS_AS_KS_WS asc;
go
/* 顯示結果：
_id         _name
----------- ------------------------------
2           國
3           人
1           中
*/

 

注意：

可以執行系統函數 fn_helpcollations 來檢索 Windows 定序和 SQL Server 定序的所有有效定序名稱的列表：

select * from fn_helpcollations()6> sp_executesql 預存程序  建議您在執行字串時，使用 sp_executesql 預存程序而不要使用 execute 語句。

由於此預存程序支援參數替換，因此 sp_executesql 比 execute 的功能更多；

由於 sql server 更可能重用 sp_executesql 產生的執行計畫，因此 sp_executesql 比 execute 更有效。

樣本：

create table #tb_suer( id int)
go
insert into #tb_suer values(1234)
go

declare @tbname nvarchar(20)
declare @sql nvarchar(500)
set @tbname='#tb_suer'
set @sql='select * from ' + @tbname
execute sp_executesql @sql
/* 結果：
id
-----------
1234
*/上面樣本示範了SQL語句的拼接。7> checksum 

--  返回按照表的某一行或一組運算式計算出來的校正和值。 checksum 用於產生雜湊索引。

checksum ( * | expression [ ,...n ] )
*    指定對錶的所有列進行計算。如果有任一列是非可比資料類型，則 checksum 返回錯誤。
      非可比資料類型有 text、ntext、image、xml 和 cursor，還包括以上述任一類型作為基底類型的 sql_variant。
expression    除非可比資料類型之外的任何類型的運算式。

樣本：

-- 找出在T1有，T表沒有的記錄。
select * from t1 where checksum(*) not in ( select checksum(*) from t )

上面樣本，等於是把t1表裡的一行資料hash和t表一行資料hash後相比，就是說兩個表裡有沒有行完全相當的。