SQLServer useful functions

xiaoxiao2021-03-06  52

Isnull

Replace NULL with the specified replacement value.

grammar

ISNULL (Check_Expression, Replacement_Value)

parameter

Check_expression

Will be checked whether it is NULL expression. Check_expression can be any type.

Replacement_Value

The expression will return when check_expression is NULL. Replacement_Value must have the same type as Check_expressSION.

Return type

Returns the same type as check_expression.

Comment

If check_expression is not null, then returns the value of the expression; otherwise returns Replacement_Value.

Example

A. Use ISNULL with AVG

The following example looks out the average price of all books, replacing all Null entries in the Price column of the Titles table with a value of $ 10.00.

USE PUBS

Go

SELECT AVG (ISNULL (Price, $ 10.00))

From titles

Go

The following is the result set:

----------------------------

14.24

(1 row (s) affected)

B. Using ISNULL

The following example selection book name, type, and price of all books in the Titles table. If the price of a book name is NULL, then the price displayed in the result set is 0.00.

USE PUBS

Go

SELECT SUBSTRING (Title, 1, 15) As Title, Type As Type,

ISNULL (Price, 0.00) AS PRICE

From titles

Go

The following is the result set:

Title Type Price

----------------------------------------------- ---

The busy execut business 19.99

Cooking with co business 11.95

You can Combat Business 2.99

Straight Talk a Business 19.99

Silicon Valley MOD_COOK 19.99

The Gourmet Mic MoD_cook 2.99

THE PSYCHOLOGY Undecided 0.00

But IS it user popular_comp 22.95

SECRETS of Sili Popular_comp 20.00

Net etiquette popular_comp 0.00

Computer Phobic Psychology 21.59

IS Anger the en psychology 10.95

Life without fe Psychology 7.00

ProLonged Data Psychology 19.99

Emotional Secur Psychology 7.99

Onions, Leeks, Trad_cook 20.95

Fifty Years in trad_cook 11.95

Sushi, Anyone? Trad_cook 14.99

(18 row (s) affected)

转载请注明原文地址:https://www.9cbs.com/read-116644.html

New Post(0)