A few days ago encountered a problem about Oracle is not equal to the last search for a moment, found the following information, to share with you, need friends can refer to the next
is not equal to the number in Oracle:
in Oracle,
<>
!=
~=
^=
are not equal to numbers. can be used.
But strange yes, I want to take out a product that price is not 180000: (Price is number type)
SELECT ID, name from product where price<> 18 0000; When the
executes this statement, the Priceis null is not logged. That is, you can't get a commodity that price is null. You must use:
SELECT ID, name from product where price<> 180000 or price is null; Just fine. The
string field has the same problem. The
remembers that null can only be judged by IS null or is not NULL, and that other operators and null operations are false.
==============================================================
Test: SELECT * From test where name<> ' xn '. Only records with a name that are not empty can be detected. Get rid of name<> ' xn '. There is a problem with this type of wording. The
then uses the InStr (name, ' xn ') = 0来 to determine if the name is not empty, the judgment is valid. If name is empty, the judgment goes wrong again. Forced to take InStr (name, ' xx '), ' xn ') = 0来 judgment, because even if the name is empty, and ' xx ' after the connection, it will not be empty.
so the last SQL statement is:
select * FROM Test where InStr (concat (name, ' xx '), ' xn ') = 0 to query the name field Not equal to ' xn ' records.
or you can use the selECT * FROM test where NVL (name, ' xx ') <> ' xn ' to query for records that are not equal to ' xn ' in the Name field.