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
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 '无效的日期'
print '有效的日期'
else
print '无效的日期'
上面的示例使用 isdate 测试某一字符串是否是有效的 datetime。
3> patindex( '%pattern%' , expression )
-- 返回指定表达式中某模式第一次出现的起始位置;
-- 如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。
'pattern' : 一个通配符字符串。pattern 之前和之后必须有 % 字符(搜索第一个或最后一个字符时除外)。
expression : 通常为要在其中搜索指定模式的字符串数据类型列。
示例:select patindex('%BB%','AA_BB_CC_DD_AA_BB_CC_DD')
-- 返回:4
-- 返回:4
上面示例返回的是第一个‘BB’的开始位置。
其实,使用 charindex 函数也能实现上面示例的查询,如下:
select charindex('BB','AA_BB_CC_DD_AA_BB_CC_DD')
--返回:4
--返回: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
-- 返回: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
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 中
*/
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
*/
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 除非可比数据类型之外的任何类型的表达式。
示例:* 指定对表的所有列进行计算。如果有任一列是非可比数据类型,则 checksum 返回错误。
非可比数据类型有 text、ntext、image、xml 和 cursor,还包括以上述任一类型作为基类型的 sql_variant。
expression 除非可比数据类型之外的任何类型的表达式。
-- 找出在T1有,T表没有的记录。
select * from t1 where checksum(*) not in ( select checksum(*) from t )
select * from t1 where checksum(*) not in ( select checksum(*) from t )
上面示例,等于是把t1表里的一行数据hash和t表一行数据hash后相比,就是说两个表里有没有行完全相当的。
本文由用户 jopen 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
转载本站原创文章,请注明出处,并保留原始链接、图片水印。
本站是一个以用户分享为主的开源技术平台,欢迎各类分享!