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.