isnull - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

isnull

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2008-10-22 : 01:56:26

isnull is to handle for only one value(ie null)
if i want to handle null and zero same time,
is there any possible to do this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 01:57:42

yup. do like below

isnull(nullif(field,0),yourvalue)

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-22 : 02:05:14

similarly u can use this also

SELECT REPLACE(ISNULL(field,0),0,yourvalue)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:15:21

quote:
Originally posted by raky

similarly u can use this also

SELECT REPLACE(ISNULL(field,0),0,yourvalue)

This will cause value to be converted to varchar type because of replace() and will cause problems if you're trying to do some arithmetic operations with value. see below example

declare @n int,@k int
select @n=NULL,@k=NULL
select replace(isnull(@n,0),0,-99)+replace(isnull(@n,0),0,100)

it will return string value -99100 as opposed to 1 as it interprets value as varchar and causes concatenation

raky
Aged Yak Warrior

767 Posts

Posted - 2008-10-22 : 02:33:18

quote:
Originally posted by visakh16

quote:
Originally posted by raky

similarly u can use this also

SELECT REPLACE(ISNULL(field,0),0,yourvalue)

This will cause value to be converted to varchar type because of replace() and will cause problems if you're trying to do some arithmetic operations with value. see below example

declare @n int,@k int
select @n=NULL,@k=NULL
select replace(isnull(@n,0),0,-99)+replace(isnull(@n,0),0,100)

it will return string value -99100 as opposed to 1 as it interprets value as varchar and causes concatenation

Thanks Visakh for pointing error with my solution..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 02:35:44

quote:
Originally posted by raky
Thanks Visakh for pointing error with my solution..

No worries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources