If Null, use a different text - 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

If Null, use a different text

Author

Topic

richardlaw
Yak Posting Veteran

68 Posts

Posted - 2012-10-14 : 15:26:31

Hi

I've created a view which I'm using for a drop down menu in a web application. In my drop down menu, I've built up a collection of columns to make one view column which I use as part of the drop down menu. It all works fine, until on of the columns in the group is Null.

Here is an example extract from the select statement from the view:


dbo.tbl_Locations.Location_Value + ': ' + dbo.tbl_Weekdays.Weekday_Short + ' ' + PARSENAME(RIGHT (REPLACE(CONVERT (VARCHAR(26), dbo.tbl_Classes.Class_TimeFrom, 100), ' ', '.'), 7), 1) + ' - ' + PARSENAME(RIGHT (REPLACE(CONVERT (VARCHAR(26), dbo.tbl_Classes.Class_TimeTo, 100), ' ', '.'), 7), 1) + ' (' + dbo.tbl_Disciplines.Discipline_Value + ' with ' + dbo.tbl_Members.Member_FirstName AS ClassInfo

In this case, if 'dbo.tbl_Members.Member_FirstName' is NULL, the whole block is null.

Is there a way that as part of the view select statement I can include the Member_FirstName, but if it's NULL, replace with 'No Coach assigned'?

Thanks as alwasy

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 15:52:27

Use COALESCE or ISNULL function for each of the columns that may/can contain NULLs. For example:

COALESCE(dbo.tbl_Locations.Location_Value,'') + ': ' + COALESCE(dbo.tbl_Weekdays.Weekday_Short,'') + ' ' +

or perhaps

COALESCE(dbo.tbl_Locations.Location_Value,'DefaultLocation') + ': ' + COALESCE(dbo.tbl_Weekdays.Weekday_Short,'DefaultWeekday') + ' ' +

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources