IsNull function in DB2 SQL?

0 votes
asked Sep 15, 2008 by dave

Is there a performant equivalent to the isnull function for DB2?

Imagine some of our products are internal, so they don't have names:

Select product.id, isnull(product.name, "Internal) 
From product

Might return:

1 Socks 
2 Shoes 
3 Internal 
4 Pants

6 Answers

0 votes
answered by md-kamruzzaman

In DB2 there is a function NVL(field, value if null).

Example:

SELECT ID, NVL(NAME, "Internal) AS NAME, NVL(PRICE,0) AS PRICE FROM PRODUCT WITH UR;

0 votes
answered Sep 2, 2008 by venkatram

I think COALESCE function partially similar to the isnull, but try it.

Why don't you go for null handling functions through application programs, it is better alternative.

0 votes
answered Sep 2, 2008 by fuangwith-s

COALESCE function same ISNULL function Note. you must use COALESCE function with same data type of column that you check is null.

0 votes
answered Sep 15, 2008 by chris-shaffer

I'm not familiar with DB2, but have you tried COALESCE?

ie:


SELECT Product.ID, COALESCE(product.Name, "Internal") AS ProductName
FROM Product
0 votes
answered Sep 14, 2009 by madmurf

For what its worth, COALESCE is similiar but

IFNULL(expr1, default)

is the exact match you're looking for in DB2.

COALESCE allows multiple arguments, returning the first NON NULL expression, whereas IFNULL only permits the expression and the default.

Thus

SELECT product.ID, IFNULL(product.Name, "Internal") AS ProductName
FROM Product

Gives you what you're looking for as well as the previous answers, just adding for completeness.

0 votes
answered Sep 26, 2014 by jnn
Select Product.ID, VALUE(product.Name, "Internal") AS ProductName from Product
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...