Server函数之空值处理,coalesce系统函数简介

转自:http://www.maomao365.com/?p=4390

利用nullif函数

coalesce( expression [ ,...n ] )重临其参数中第三个非空表明式。

下表列出 SET 与 SELECT 的分别

一、coalesce函数简单介绍

nullif函数有八个参数,定义如下:

 

  SELECT SET
同时对多个变量同时赋值时 支持 不支持
表达式返回多个值时 将返回的最后一个值赋给变量 出错
表达式未返回值时 变量保持原值 变量被赋null值

coalesce 系统函数,比ISNULL越来越强劲,更有助于的系统函数,
coalesce能够收到七个参数,重返最左侧不为NULL的参数,当全部参数都为空时,则赶回NULL
coalesce是最优isnull写法实施方案
原先大家使用isnull对两列或多列数据开展为空重回时候,要求频仍运用isnull函数
—————————————————————————-
例:
declare @a varchar(10),@b varchar(10),@c varchar(10)
当@a为null时,大家查阅@b是或不是为NULL,不为null,则赶回@b ,不然查看@c 不为NULL,则赶回@c ,不然再次来到NULL

NULLIF( expression , expression )

Select coalesce(null,null,'1','2') //结果为 1

(1).相同的时间对五个变量同不时间赋值时

select isnull(@a,isnull(@b,isnull(@c,null)))
/*当需判别的参数越多时,大家的函数表明式就能变的百般复杂*/

其功能正是:假如八个内定的表明式相等,就回来null值。

coalesce(expression1**,**...n) 与此 case函数等效:

declare @a varchar(128), @b varchar(128) SET @a='ABC',@b='EFG' GO --报错:音讯 102,等级 15,状态 1,第 3 行 ',' 紧邻有语法错误。

但我们选用coalesce函数,会使此 表明式变的雅观,老妪能解
select coalesce(@a,@b,@c)
——————————————————————————–

看一演示:

 

declare @a varchar(128), @b varchar(128) SELECT @a='ABC',@b='EFG' GO --准确运转


select nullif(0,0)

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL
END

(2).表明式重回三个值时

二、coalesce 应用比方

运作结果:null

 

在验证这一项前大家先成立二个要用到的表,并对其赋值,代码如下:

 

咱俩掌握,null与其余数进行别的运算,其结果都极其null,利用那一点,大家得以将地点的sql改写为:

注意:
当首个表明式为字符串且无法转化为整数时,若在前面包车型地铁表达式中有整数,那样的说话是会报错的。
例如:

 

  declare @a varchar(10),@b varchar(10),@c varchar(10),@d int 

 select coalesce(@a,@b,@c)

 set @a ='g'
 select coalesce(@a,@b,@c)

 set @a =null 
 set @b ='g2'
 set @c ='g3'
 select coalesce(@a,@b,@c)

 set @a =null
 set @b =null
 set @c =null 
 set @d =100

 select coalesce(@a,@b,@c,@d)

declare @a int;

Select COALESCE('a',null,'1',2) //那是不当的

图片 1图片 2赋值测验表--fuzhiTEST

 

declare @b int;

这一定于

图片 3

set @a=10

 

create table fuzhiTEST(
id int ,
name varchar(128)
)
go
insert into fuzhiTEST(id,name) values(1,'name1')
insert into fuzhiTEST(id,name) values(2,'name2')
insert into fuzhiTEST(id,name) values(3,'name3')
go

set @b=0

select 
CASE
WHEN ('a' IS NOT NULL) THEN 'a'
WHEN (2 IS NOT NULL) THEN 2
ELSE NULL
END //会出现谬误,因为系统不能够将a转变为相应的整数

图片 4

select @a/nullif(@b,0)

 

 

其运维结果当然为null了。

但是

declare @name varchar(128) set @name = (select name from fuzhiTEST) GO  --报错:消息 512,级别 16,状态 1,第 2 行        

再使用isnull函数,大家就可以完结当@b=0的时候,结果回到1的必要了。最后的sql改写如下:

 

子查询再次来到的值持续三个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表明式时,这种气象是差异意的。

declare @a int;

select 
CASE
WHEN ('a' IS NOT NULL) THEN 'a'
WHEN (2 IS NOT NULL) THEN '2'
ELSE NULL
END //那是不错的

declare @name varchar(128) select @name =name from fuzhiTEST print @name GO  --正确运转:显示 name3

declare @b int;

 

(3).表明式未重回值时

set @a=10

故此大家在使用coalesce函数时,应该格外小心。
isnull( check_expression , replacement_value ) 使用钦点的替换值替换 NULL。
replacement_value 必需是足以隐式调换为 check_expresssion 类型的花色。
在表stu中
图片 5

declare @name varchar(128) set @name = 'SET开首值' set @name = (select name from fuzhiTEST where id = 4 ) print @name GO   --精确运维:展现NULL

set @b=0

select isnull(ssex,'p') from stu //若ssex列中有NULL值,那么再次回到p

declare @name varchar(128) set @name = 'SELECT发轫值' select @name = name from fuzhiTEST where id = 4 print @name --正确运营:展现 SELECT开首值

select isnull(@a/nullif(@b,0),1)

nullif( expression , expression ) 假如四个钦定的表达式相等,则赶回空值,否则 NULLIF 重返第贰个 expression 的值。

留神:SELECT 也得以将标量子查询的值赋给变量,假设标量子查询不重临值,则变量被置为 null 值(此时与行使 SET 赋值是千篇一律的)。 对标量子查询的概念大家恐怕感觉面生,上面举个例子表达下(比较上面古铜黑部分):

OK,二种办法介绍实现,相对于方法一,小编更推荐大家利用方法二,因为方法二的代码更简省。

select nullif(1,1) //结果为 NULL
select nullif(1,2) //结果为 1

declare @name varchar(128) set @name = 'SELECT早先值' select @name =(select name from fuzhiTEST where id = 4 ) print @name --精确运维:显示NULL

(尽管有对象对isnull函数不了然的话,这里能够解释一下。

来源

isnull的定义如下:

 

isnull(参数1,参数2)

 

其功效正是,当参数1对等null时,就回去参数2的值。)

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Server函数之空值处理,coalesce系统函数简介

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。