What happens when a SQL fuzzy query encounters a null value?

Iamlaosong

SQL query statement using% to do fuzzy query, the program generally requires users to enter some information, based on this information fuzzy query. For example, user input 340104, the following statement is to query yesterday customer code 340104 beginning with all the message information:

SELECT * from  tb_evt_mail_clct t where t.clct_date = Trunc (sysdate-1) and   t.sender_cust_code like ' 340,104% '

When the user does not enter anything need to query yesterday all the message information, the following statement does not query all information, this statement can only find all the big customer's mail information, unable to find retail e-mail information:

SELECT * from  tb_evt_mail_clct t where t.clct_date = Trunc (sysdate-1) and   t.sender_cust_code like '% '

This is because the retail customer code is NULL, and the following statement can take into account both of the above two scenarios (with 0000 for null values):

Has a limit value:

SELECT * from  tb_evt_mail_clct t where t.clct_date = trunc (sysdate-1)   and NVL (t.sender_cust_code, ' 0000 ') LIKE ' 3 40,104% '

Infinite value:

SELECT * from  tb_evt_mail_clct t where t.clct_date = trunc (sysdate-1)   and NVL (t.sender_cust_code, ' 0000 ') like '% '

The limit value is 0000 When the result is all retail:

SELECT * from  tb_evt_mail_clct t where t.clct_date = trunc (sysdate-1)   and NVL (t.sender_cust_code, ' 0000 ') like ' 0 0% '

What happens when a SQL fuzzy query encounters a null value?