SQL Server 中几个有用的特殊函数

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:


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




本文转自钢钢博客园博客,原文链接http://www.cnblogs.com/xugang/archive/2011/10/18/2216582.html,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
21天前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
27天前
|
SQL 数据库 数据库管理
数据库SQL函数应用技巧与方法
在数据库管理中,SQL函数是处理和分析数据的强大工具
|
28天前
|
SQL 数据库 索引
SQL中COUNT函数结合条件使用的技巧与方法
在SQL查询中,COUNT函数是一个非常常用的聚合函数,用于计算表中满足特定条件的记录数
|
28天前
|
SQL 关系型数据库 MySQL
SQL日期函数
SQL日期函数
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
2月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
3月前
|
SQL 数据处理 数据库
SQL中的函数有哪些类型
【8月更文挑战第20天】SQL中的函数有哪些类型
36 1
|
3月前
|
SQL 数据处理 数据库
|
3月前
|
SQL Oracle 关系型数据库
SQL 中的大小写处理函数详解
【8月更文挑战第31天】
115 0
|
3月前
|
SQL 数据采集 数据挖掘
为什么要使用 SQL 函数?详尽分析
【8月更文挑战第31天】
45 0