In Mysql, The ifnull () function is similar to the nvl () function, and nvlifnull

Source: Internet
Author: User

In Mysql, The ifnull () function is similar to the nvl () function, and nvlifnull

IFNULL (expr1, expr2)

If expr1 is not NULL, IFNULL () returns expr1; otherwise, it returns expr2. IFNULL () returns a number or string value, depending on the context in which it is used.

mysql> select IFNULL(1,0);    -> 1mysql> select IFNULL(0,10);    -> 0mysql> select IFNULL(1/0,10);    -> 10mysql> select IFNULL(1/0,'yes');    -> 'yes' IF(expr1,expr2,expr3) 

IF expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF () returns expr2; otherwise, expr3 is returned. IF () returns a number or string value, depending on the context in which it is used.

mysql> select IF(1>2,2,3);    -> 3mysql> select IF(1<2,'yes','no');    -> 'yes'mysql> select IF(strcmp('test','test1'),'yes','no');    -> 'no'

Expr1 is calculated as an integer, which means that if you are testing a floating point or string value, you should use a comparison operation.

mysql> select IF(0.1,1,0);    -> 0mysql> select IF(0.1<>0,1,0);    -> 1

In the first case above, IF (0.1) returns 0, because 0.1 is converted to an integer, resulting in testing IF (0 ). This may not be what you expected. In the second case, compare and test the original floating point value to see if it is non-zero. The comparison result is used as an integer.

CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END   CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END 

The first version returns result, where value = compare-value. In the second version, if the first condition is true, result is returned. If no matching result value exists, the result after ELSE is returned. If no ELSE part exists, NULL is returned.

mysql> SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;    -> "one"mysql> SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;    -> "true"mysql> SELECT CASE BINARY "B" when "a" then 1 when "b" then 2 END;-> NULL

In the above discussion, the ifnull () function in Mysql, similar to nvl () function, is all the content that I have shared with you. I hope you can give me a reference and support the help house.

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.