An analysis of the not equal number _oracle in Oracle

Source: Internet
Author: User
About the Not equal number in Oracle:
In Oracle,
<>
!=
~=
^=
is not equal to the meaning of the number. can be used.
But it's weird. Yes, I want to take out a product that price is not 180000: (Price is of type number)
SELECT ID, name from product where Price<> 180000;
When executing this statement, Priceis NULL is not logged. That is, you can't get a commodity that price is null. Must use:
SELECT ID, name from product where price<> 180000 or price is null;Just fine.
The same problem exists with the field of the string.
Remember:Null can only be judged by IS null or is not NULL, and the 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.
Then use the InStr (name, ' xn ') = 0来 to judge 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 final SQL statement is:
SELECT * FROM Test where InStr (concat (name, ' xx '), ' xn ') = 0To query for records that are not equal to ' xn ' in the Name field.
Or you can use SELECT * FROM Test where NVL (name, ' xx ') <> ' xn 'To query for records that are not equal to ' xn ' in the Name field.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.