Oracle Pit-empty string with null

empty string with null

First there is the following code

SELECT *From Pdc_datadomain DDINNERJOIN pdc_dd_table DDTOn DD. Datadomainid = DDT. Datadomainidand DD. Applicationdbid =  ' 3e7c6764d73f4c7786c99e5b72eb6912 ' AND DDT. TableName <>  ' # ' and not Span class= "Hljs-keyword" >exists (select * FROM pdc_ application_3e7c6764 A where a.tablename = DDT. TableName and a.fieldname = DDT. FieldName and a.fieldtype = DDT. FieldType and nvl (a.fieldlength, and NVL (A.fieldscale,           

The core focus in this sentence AND nvl(A.FieldScale, ‘‘) = nvl(DDT.FieldScale, ‘‘) , when both a FieldScale and DDT FieldScale are empty, will appear after both NVL return ", but the characteristics of Oracle is" will be processed to NULL, and Null is not equal to any value, including itself, then over, If the above should be how to deal with this, it is very simple, conversion can be converted into a program will not appear in the value can be, if the program logic does not appear value, it is only used isnull to judge.

The following are references:

--Oracle treats the empty string as null, and the test script is as follows:

select nvl(null,-1) from dual;sele‘‘,‘-1‘) from dual;select nvl(nvl(null,‘‘),-1) from dual;select nvl(trim(‘ ‘),‘-1‘) from dual;ct nvl(

--but remember, null and any value do a logical operation with the result of false, including and null itself:

Select NVL (Max' 1 '),-1)From dualwhereNULL =‘‘;Select NVL (Max' 1 '),-1)From dualwhereNull <>‘‘;Select NVL (Max' 1 '),-1)From dualwhereNULL ='-1 ';Select NVL (Max ' 1 '),-1) from dual where null <>  '-1 ';  select nvl (max ( ' 1 '),-1) from dual where null = null; select nvl (max ( ' 1 '),-1) from dual where null <> null;        

--however, when judged with IS null, the empty string and null are null:

select nvl(max(‘1‘),-1) from dual where ‘‘ is null;select nvl(max(‘1‘),-1) from dual where null is null;

--also remember that null and any numeric values are mathematically calculated, and the result is null:

select nvl(null + 0,-1) from dual;

--However, you can use | | Concatenate an empty string or null and string

select ‘a‘ || null || ‘b‘ from dual;

--Also, the result is null as an argument to the following Oracle built-in function:

Select NVL (Length (NULL),-1)from dual;Select NVL (Trim (NULL),-1)from dual;Select NVL (LTrim (NULL),'-1 ')from dual;Select NVL (RTrim (NULL),'-1 ')from dual;Select NVL (RTrim (Null‘ ‘),'-1 ')from dual;Select NVL (Soundex (NULL),'-1 ')from dual;Select NVL (SUBSTR (Null1),-1)from dual;Select NVL (INSTR (Null1),-1)from dual;Select NVL (ReplaceNull' A ',' B '),'-1 ')from dual;Select NVL (MinNULL),'-1 ')from dual;select nvl (max (null),  '-1 ') from dual; select nvl (sum (null),  '-1 ') from dual; select nvl (avg (null),  '-1 ') from dual; select nvl (sum (null),  '-1 ') from dual;         

--however, the result is not NULL as an argument to the following Oracle built-in function:

select concat(‘a‘, null) from dual;select concat(null, ‘a‘) from dual;select count(null) from dual;

--when other functions are used, you can also use the methods above to test

Source: http://edgenhuang.iteye.com/blog/975567