SqlNULL value Processing Method

Source: Internet
Author: User
The SQL null value processing method isnull in SQL Server. In this example, there is a column that records the number of articles accessed. What I want to implement now is to refresh the page every time. Number of visits + 1. SQL statement. art_count indicates the number of accesses. It is of the int type. Updatearticlesetart_count & quot; (art_count + 1) whereart_id & quot; 3. If the art_count SQL null value is processed

An example of isnull in sqlserver

There is a column in it to record the number of visits to the article. What I want to implement now is to refresh the page every time. Number of visits + 1. SQL statement. art_count indicates the number of accesses. It is of the int type.
Update article set art_count = "(art_count + 1) where art_id =" 3 but it does not work if art_count is null.
If decode is used for oracle, this function can be easily implemented. How does sqlserver implement similar functions?
Sqlserver has a function isnull, which has two parameters: isnull (p1, p2). If p1 is null, it is replaced by p2.
This function is similar to the nvl of oracle. For example
Select avg (isnull (price, $10.00) from titles is inspired by this function to write SQL statements like this
Update article set art_count = "(isnull (vote_count, 0) + 1) where art_id =" 3"


-Determines whether some fields are empty.
-- Case
Select case when 'field name' is null then 'n' else convert (varchar (20), 'field name') end as 'newname'
Select case when null is null then 'n' 'else convert (varchar (20), null) end as 'newname'

-- SQL server 2005: coalesce
Select coalesce ('string type field', 'n') as 'newname'
Select coalesce (convert (varchar (20), 'non-string type field'), 'n') as 'newname'
Select coalesce (convert (varchar (20), null), 'n') as 'newname'

-- Coalesce: returns the first non-null expression in the parameter.
Select coalesce (null, null, 1, 2, null) union
Select coalesce (null, 11,12, 13, null) union
Select coalesce (111,112,113,114, null)


Null does not affect all statistical functions. In general. When calculating the average value (avg), the null value will certainly have an impact. When calculating the minimum value (min), the null value may have an impact on min, which is a random attribute in my opinion; when the maximum value (max) or statistical value (null) is counted, the null value has no effect on it.

So there is another saying: null values do not participate in statistics, do not participate in calculation, and can only be judged by is.

Statement for determining the null value: select * from table where field is null;

Convert a null value statement: select Field 1, Field 2, Field 3, is null (Field 3, 'a value') from table;

In short, we must take the null value seriously. It is best to add is null when using the statistical function to prevent unexpected occurrence.

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.