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后相比，就是说两个表里有没有行完全相当的。