proper syntax for 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

proper syntax for isnull?

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 15:46:45

In using the following script, i'm pretty sure that if any part of the concantenation has a null value, then it results in the final string being nuked to null:
UPDATE arr_pc SET probcause=Cast(warrantnarrative AS VARCHAR(MAX))+'. '+CAST(Misc AS VARCHAR(MAX))+'. Codefendants in this cause are: '+cast(CoDefendants AS VARCHAR(MAX))+'Additional Information provided: '+CAST(magistrate AS VARCHAR(MAX))

What's the proper syntax for wrapping the individual parts in ISNULL? Or is there a better way to avoid the nuked issue?

thanks

james

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-12 : 15:50:29

ISNULL(<column name>, <replacement value>)

Aside: Varchar(max)? Expecting 2 billion character long strings where? What type is probcause? What type are the other columns?

--
Gail Shaw
SQL Server MVP

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 15:52:42

probcause is a varchar max. the others are deprecated text columns. some are laboriously long.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 16:03:30

ok, so why the flip am i getting "incorrect syntax near','" with this?:

UPDATE arr_pc SET probcause=Cast(isnull(warrantnarrative AS VARCHAR(MAX), ''))+'. '+CAST(isnull(Misc AS VARCHAR(MAX), ''))+'. Codefendants in this cause are: '+cast(isnull(CoDefendants AS VARCHAR(MAX) , ''))+'Additional Information provided: '+CAST(isnull(magistrate AS VARCHAR(MAX), ''))

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-12 : 16:16:37

Because you have Isnull(<column name> AS <data type>, <replacement value>). You've merged the functions together with the new data type within the isnull, which makes no sense, the replacement data type is part of the cast function.

ISNULL(<Column name>,<replacement value>). You can then cast the result of that as whatever you like.

--
Gail Shaw
SQL Server MVP

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 16:31:17

i swear i'm trying here. i really am. But following what i think i'm reading with your guidance, using:
UPDATE arr_pc SET probcause=ISNULL(warrantnarrative,'')CAST (Warrantnarrative AS VARCHAR(MAX))+'. '+ISNULL(MISC,'')CAST(Misc AS VARCHAR(MAX))+'. Codefendants in this cause are: '+ISNULL(CoDefendants,'')CAST (Codefendants AS VARCHAR(MAX)))+'Additional Information provided: '+isnull(magistrate,'') CAST magistrate AS VARCHAR(MAX))

I get "incorrect syntax near 'CAST'" now.

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-12 : 16:39:55

disregard. got it with:
UPDATE arr_pc SET probcause=ISNULL(cast(warrantnarrative AS VARCHAR(MAX)),'')+'. '+ISNULL(cast(MISC AS VARCHAR(MAX)),'')+'. Codefendants in this cause are: '+ISNULL(Cast(CoDefendants AS VARCHAR(MAX)),'')+'Additional Information provided: '+isnull(cast(magistrate AS VARCHAR(MAX)),'')

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-12 : 16:49:06

Or
...
SET probcause=cast(ISNULL(warrantnarrative,'') AS VARCHAR(MAX)) + ...

--
Gail Shaw
SQL Server MVP

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-03-13 : 09:52:32

Sometimes i hate it when there's more than one way to skin this cat. Thanks for your help.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-03-13 : 10:25:49

In some cases the results will differ depending which way around the functions are nested. Not in this case though.

--
Gail Shaw
SQL Server MVP