A few days ago encountered a question about Oracle does not equal, finally searched, found the following information, bring to share with you, the need for friends can refer to the next
about the non-equal number in Oracle:
In Oracle,
<>
!=
~=
^=
is not equal to the meaning of the number. can be used.
But strangely yes, I want to take out price when it's not 180000: (Price is number type)
SELECT ID, name from product where Price<> 180000;
When executing this statement, the Priceis null record does not appear. That is, you cannot get the goods that price is null. Must use:
SELECT ID, name from product where price<> 180000 or price is null;Just fine.
The same problem exists for the field of the string.
Remember:Null can only be judged by IS null or is not NULL, and other operators and null operations are false.
==============================================================
Test:SELECT * FROM test where name<> ' xn '. Only name non-empty records can be found. Get rid of name<> ' xn '. There is a problem with this notation.
Then use the InStr (name, ' xn ') = the null to determine if the name is not empty, the judgment is still valid. If name is empty, the judgment goes wrong. Have to take InStr (Concat (name, ' xx '), ' xn ') = zero, because even if the name is empty, and ' xx ' after the connection, it will not be empty.
so the final SQL statement is:
SELECT * FROM Test where InStr (concat (name, ' xx '), ' xn ') = 0To query for records where the name field is not equal to ' xn '.
Or you can useSELECT * FROM Test where NVL (name, ' xx ') <> ' xn 'To query for records where the name field is not equal to ' xn '.
An analysis of the non-equal numbers in Oracle
