欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

在oracle中用like模糊搜索时如何使其能搜到空值

程序员文章站 2024-01-18 12:59:28
...

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执行模糊查找是,发现数据量不对,表的结构如下:

在oracle中用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
  );

在oracle中用like模糊搜索时如何使其能搜到空值

当我用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在oracle中用like模糊搜索时如何使其能搜到空值在oracle中用like模糊搜索时如何使其能搜到空值

  1. select * from tb_evt_mail_clct t
  2. where t.clct_date = trunc(sysdate - 1)
  3. and t.sender_cust_code like '340104%'

当用户什么都不输入需要查询昨天所有邮件信息时,下面的语句并不能查询到所有信息,这条语句只能查到所有大客户的邮件信息,查不到散户的邮件信息:

[sql] view plaincopy在oracle中用like模糊搜索时如何使其能搜到空值在oracle中用like模糊搜索时如何使其能搜到空值

  1. select * from tb_evt_mail_clct t
  2. where t.clct_date = trunc(sysdate - 1)
  3. and t.sender_cust_code like '%'

这是因为散户的客户代码为空值,下面这条语句可以同时兼顾上面两种情形(假定用0000代表空值):

有限定值:

[sql] view plaincopy在oracle中用like模糊搜索时如何使其能搜到空值在oracle中用like模糊搜索时如何使其能搜到空值

  1. select * from tb_evt_mail_clct t
  2. where t.clct_date = trunc(sysdate - 1)
  3. and nvl(t.sender_cust_code,'0000') like '340104%'

无限定值:

[sql] view plaincopy在oracle中用like模糊搜索时如何使其能搜到空值在oracle中用like模糊搜索时如何使其能搜到空值

  1. select * from tb_evt_mail_clct t
  2. where t.clct_date = trunc(sysdate - 1)
  3. and nvl(t.sender_cust_code,'0000') like '%'

限定值是0000时结果是所有散户:

[sql] view plaincopy在oracle中用like模糊搜索时如何使其能搜到空值在oracle中用like模糊搜索时如何使其能搜到空值

  1. select * from tb_evt_mail_clct t
  2. where t.clct_date = trunc(sysdate - 1)
  3. and nvl(t.sender_cust_code,'0000') like '0000%'

除了like,not like、not in 、等运算符号也都不包含空值,例如下面语句并不包含客户代码为空值的记录:

[sql] view plaincopy在oracle中用like模糊搜索时如何使其能搜到空值在oracle中用like模糊搜索时如何使其能搜到空值

  1. select * from tb_evt_mail_clct t
  2. where t.clct_date = trunc(sysdate - 1)
  3. and t.sender_cust_code '34122600200300'

要想包含,同样需要将条件改为:nvl(t.sender_cust_code,'0000') '34122600200300'

总之,当一个字段为空值时,表达式中无论是等于还是不等于,结果都为假,只有 is null结果为真。