sql 2005 catenating strings with NULL values

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

SQL Server 2005 Forums

Transact-SQL (2005)

sql 2005 catenating strings with NULL values

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-28 : 00:56:13

I need the result to return the catenated name even if first, middle or last name is NULL.

Here is the current result which just returns NULL if any of the values are NULL. It would be best if it could also deal with any of the first 128 ASCII character set also?

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[DealwithNULL] Script Date: 08/28/2008 13:46:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[DealwithNULL]

AS
--SET NOCOUNT ON
SELECT UserDetails.LastName + ' ' + UserDetails.MiddleName + ' ' + UserDetails.FirstName AS Contact
FROM UserDetails
--SET NOCOUNT OFF

Return

But this returns this, the 4th name is NULL, when the data is firstname=a, middlename=NULL, lastname=c:

Running [dbo].[DealwithNULL].

Contact
--------------------------------------------------------------------------------------------------------------------------------------------------------
a b c
a a a
b a a
<NULL>
a a z
No rows affected.
(5 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[DealwithNULL].

I have tried the following approaches with these bad results, which is the best way to go?

Option 1.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[DealwithNULL] Script Date: 08/28/2008 13:46:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[DealwithNULL]

AS
--SET NOCOUNT ON
SELECT UserDetails.FirstName, UserDetails.MiddleName, UserDetails.LastName, Coalesce(UserDetails.LastName + ' ' + UserDetails.MiddleName + ' ' + UserDetails.FirstName, LastName, MiddleName, FirstName) AS Contact
FROM UserDetails
--SET NOCOUNT OFF

Return

But this gives this, in the 4th name the lastname is present but middle and first name is blank, only middle name is NULL, last should be present:

Running [dbo].[DealwithNULL].

FirstName MiddleName LastName Contact
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------
c b a a b c
a a a a a a
a a b b a a
a <NULL> c c
z a a a a z
No rows affected.
(5 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[DealwithNULL].

Option 2.

USE [VC]
GO
/****** Object: StoredProcedure [dbo].[DealwithNULL] Script Date: 08/28/2008 13:46:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[DealwithNULL]

AS
--SET NOCOUNT ON
SELECT UserDetails.LastName + ' ' + NewMiddleName + ' ' + UserDetails.FirstName AS Contact,
Case
When UserDetails.MiddleName IS NULL Then '' Else UserDetails.MiddleName
End As NewMiddleName
FROM UserDetails
--SET NOCOUNT OFF

Return

But this gives error, how do I reference the alias:

Msg 207, Level 16, State 1, Procedure DealwithNULL, Line 6
Invalid column name 'NewMiddleName'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 01:01:54

You can deal with NULL data via COALESCE/ISNULL or you can use the following SET option: CONCAT_NULL_YIELDS_NULL. See BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-28 : 05:57:52

Thanks tkizer. I will give it a try however how do I get the two example to work also. I would prefer not to use this function because:

In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-08-28 : 06:01:43

then use Coalesce(UserDetails.MiddleName, '')

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-28 : 06:20:24

All three of these options seem to work. Thanks

SET CONCAT_NULL_YIELDS_NULL OFF;

SELECT UserDetails.LastName + ' ' + Coalesce(UserDetails.MiddleName, '') + ' ' + UserDetails.FirstName AS Contact

SELECT UserDetails.LastName + ' ' + ISNULL(UserDetails.MiddleName, '') + ' ' + UserDetails.FirstName AS Contact

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources