help in using nvl function - 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

help in using nvl function

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-12-28 : 10:38:50

In the following query if

BOXI_REPORT_FIELD.BOXI_FIELD_TYPE is null or space then replace that with unknown

how to do this...

i am trying some thing like this

(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE = null then nvl(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,'Unknown'))or
(if BOXI_REPORT_FIELD.BOXI_FIELD_TYPE = '' then nvl(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,'Unknown'))

Thanks,
reacha

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-28 : 10:41:43

[BOXI_FIELD_TYPE ] = ISNULL(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,'Unknown')

Jim

Everyday I learn something that somebody else already knew

singularity
Posting Yak Master

153 Posts

Posted - 2010-12-28 : 10:42:06

[code]
case when isnull(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE,' ') = ' ' then 'Unknown' else BOXI_REPORT_FIELD.BOXI_FIELD_TYPE end [/code]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 10:42:59

coalesce(nullif(BOXI_REPORT_FIELD.BOXI_FIELD_TYPE.''),'unknown')

Are you using sql server or oracle?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

reacha
Starting Member

49 Posts

Posted - 2010-12-28 : 10:44:47

SQL server

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-28 : 10:51:35

then equivalent of nvl is coalesce but as you need to cater for a space as well then the nullif will convert the space to a null allowing coalesce to be used as in my previous post.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-29 : 05:42:13

or

case when BOXI_REPORT_FIELD.BOXI_FIELD_TYPE>'' then BOXI_REPORT_FIELD.BOXI_FIELD_TYPE else 'unknown' end

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources