在oracle中用like模糊搜索时如何使其能搜到空值
oracle中查询关键字 like,当我用like执行模糊查找是,发现数据量不对,表的结构如下: -- Create table create table NEOERP( ID NUMBER ( 22 ) not null , PRODUCTNAME NVARCHAR2( 100 ) default ' ' , CT_SMP_SPECIFICATION NVARCHAR2( 100 ) default ' '
oracle中查询关键字 like,当我用like执行模糊查找是,发现数据量不对,表的结构如下:
-- Create table create table NEOERP ( ID NUMBER(22) not null, PRODUCTNAME NVARCHAR2(100) default ' ', CT_SMP_SPECIFICATION NVARCHAR2(100) default ' ', TYPENAME NVARCHAR2(50), UNIT NVARCHAR2(30) default ' ', STORECODE NVARCHAR2(100) default ' ', ROOMCODE NVARCHAR2(100) default ' ', SAMPTMPLID NVARCHAR2(100), CT_BELONG NVARCHAR2(100) default ' ', CT_MANUFACTURE NVARCHAR2(100), CT_EXPORTER_PLACE NVARCHAR2(100), REMARK NVARCHAR2(200), LOGINDATE DATE default sysdate, CT_SMP_TYPE NVARCHAR2(100), SAMPLENAME NVARCHAR2(200), SAMPLEDESCRIPTION NVARCHAR2(200), STARTDATE DATE, STATUS NVARCHAR2(2) default 'F', REMOVE_DATE DATE, TIMETYPE NVARCHAR2(10), REPORTDATE NUMBER(22), TIMEVALUE NUMBER(22) ) tablespace VGSM pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the columns comment on column NEOERP.PRODUCTNAME is '产品名称'; comment on column NEOERP.CT_SMP_SPECIFICATION is '规格'; comment on column NEOERP.TYPENAME is '类别名称'; comment on column NEOERP.UNIT is '单位'; comment on column NEOERP.STORECODE is '存货编码'; comment on column NEOERP.ROOMCODE is '物料编码'; comment on column NEOERP.SAMPTMPLID is '样品模版id'; comment on column NEOERP.CT_BELONG is '归属地'; comment on column NEOERP.CT_MANUFACTURE is '生产地'; comment on column NEOERP.CT_EXPORTER_PLACE is '出口地'; comment on column NEOERP.REMARK is '备注'; comment on column NEOERP.CT_SMP_TYPE is '样品类型'; comment on column NEOERP.SAMPLENAME is '样品名称'; comment on column NEOERP.SAMPLEDESCRIPTION is '样品描述'; comment on column NEOERP.STARTDATE is '起始时间'; comment on column NEOERP.STATUS is 'f可用 状态'; comment on column NEOERP.REMOVE_DATE is '删除时间'; comment on column NEOERP.TIMETYPE is '年月日'; comment on column NEOERP.REPORTDATE is '要求报告日期'; comment on column NEOERP.TIMEVALUE is '有效期'; -- Create/Recreate primary, unique and foreign key constraints alter table NEOERP add constraint PK_ERP_ID primary key (ID) using index tablespace VGSM pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited );
当我用SELECT * FROM NEOERP WHERE ROOMCODE LIKE '%%',来查询时候,数据一直存在问题,查处的数据量与期望的数据量不同,
经查,是当roomcode为null造成的,
当我用SELECT * FROM NEOERP WHERE ROOMCODE IS NULL 来查询发现这个查询的结果是我所缺少的数据信息。
null的含义,在我们不知道具体有什么数据,也即未知,称他为空,oracle中,含有空值的表列长度为零。
等价于没有任何值,是未知数,null与0,空字符串,空格不同,对空值做运算,结果仍然是空值,oracle提供了处理空值函数nvl,比较时候用 is null或者 is not null.
通过以上说明,通过SELECT * FROM NEOERP WHERE ROOMCODE LIKE '%%'查询不到值为null的数据。
对上面sql语句进行修改后,既可以完成所需功能:
SELECT * FROM NEOERP WHERE NVL(ROOMCODE,0) LIKE '%%';
注释:
当ROOMCODE为null时,NVL(ROOMCODE,0)的返回值为0,而0正好符合匹配LIKE '%%'这个匹配模式,所以,ROOMCODE为null的数据行可以被查询出来。
附加:
空字符串"可以被like`%%`搜索到。
参考:
关于null的说明以及和0的区别
百度 like ‘%%’空值
====================================================================
SQL模糊查询碰到空值怎么办?
作者:iamlaosong
SQL查询语句用%来做模糊查询,程序中一般要求用户输入部分信息,根据这个信息进行模糊查询。例如用户输入340104,下面这条语句就是查询昨天客户代码为340104开头的所有邮件信息:
[sql] view plaincopy
- select * from tb_evt_mail_clct t
- where t.clct_date = trunc(sysdate - 1)
- and t.sender_cust_code like '340104%'
当用户什么都不输入需要查询昨天所有邮件信息时,下面的语句并不能查询到所有信息,这条语句只能查到所有大客户的邮件信息,查不到散户的邮件信息:
[sql] view plaincopy
- select * from tb_evt_mail_clct t
- where t.clct_date = trunc(sysdate - 1)
- and t.sender_cust_code like '%'
这是因为散户的客户代码为空值,下面这条语句可以同时兼顾上面两种情形(假定用0000代表空值):
有限定值:
[sql] view plaincopy
- select * from tb_evt_mail_clct t
- where t.clct_date = trunc(sysdate - 1)
- and nvl(t.sender_cust_code,'0000') like '340104%'
无限定值:
[sql] view plaincopy
- select * from tb_evt_mail_clct t
- where t.clct_date = trunc(sysdate - 1)
- and nvl(t.sender_cust_code,'0000') like '%'
限定值是0000时结果是所有散户:
[sql] view plaincopy
- select * from tb_evt_mail_clct t
- where t.clct_date = trunc(sysdate - 1)
- and nvl(t.sender_cust_code,'0000') like '0000%'
除了like,not like、not in 、等运算符号也都不包含空值,例如下面语句并不包含客户代码为空值的记录:
[sql] view plaincopy
- select * from tb_evt_mail_clct t
- where t.clct_date = trunc(sysdate - 1)
- and t.sender_cust_code '34122600200300'
要想包含,同样需要将条件改为:nvl(t.sender_cust_code,'0000') '34122600200300'
总之,当一个字段为空值时,表达式中无论是等于还是不等于,结果都为假,只有 is null结果为真。
上一篇: 王森:程序设计师忏悔录_PHP
下一篇: 无线网络优化——选择无线AP还是路由器?