SQL NULL

最新推荐文章于 2023-08-14 11:03:57 发布

weixin_30251829

最新推荐文章于 2023-08-14 11:03:57 发布

阅读量48

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/hongdada/p/3446112.html

本文介绍了如何在SQL查询中利用ISNULL和CASE WHEN语句来处理NULL值，通过示例展示了这两种方法的应用，并强调了在处理NULL值时选择合适方法的重要性。

表

select CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name= 'Alliance'

select CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name= 'Alliance' and CHARACTER_MAXIMUM_LENGTH is not null;

select CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name= 'Alliance' and CHARACTER_MAXIMUM_LENGTH is null;

当遇到Case(字段)..when..then..when.. then..else...end

 select case ISNULL(Cast(CHARACTER_MAXIMUM_LENGTH as varchar(10)),'')
       when '' then ''
       else '('+Cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))+')' end as DATA  
       from information_schema.columns 
where table_name= 'Alliance'

一定要用ISNULL转化过来，不能用when NULL

或者使用

case when field is null then...else..end

select case  
 when CHARACTER_MAXIMUM_LENGTH is null then ''
 else '('+Cast(CHARACTER_MAXIMUM_LENGTH as varchar(10))+')' end as DATA  
  from information_schema.columns 
where table_name= 'Alliance'