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

jopen 10年前發布 | 15K 次閱讀 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后相比，就是說兩個表里有沒有行完全相當的。

 本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
 轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
 本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！