今天遇到了某字段为string类型,但是里面存放了数字,null(空值),‘’(空串)在进行条件过滤时,出现了问题,现在进行一下简单的总结:
数据准备:
use default;
create table test0613 as
select '1' as code
union all
select '0' as code
union all
select null as code
union all
select '' as code ;
查询结果:
筛选不为‘0’,需要对null进行单独的操作:
select * from test0613 where code <> '0'; 通过这种方式会漏了为null的数据。
select * from test0613 where code <> '0' or code is null;
筛选空值(null):
select * from test0613 where code is null;
筛选空串(‘’)
select * from test0613 where length(code)=0;