在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 结果为真。

查看更多关于在oracle中用like模糊搜索时如何使其能搜到空值的详细内容...