Checking for Nulls in String

Hi all,

I have the following SQL string;

SELECT Title +' '+ FirstName +' '+ MiddleName +' '+ LastName AS PersonsName FROM MyData...Contacts;

I want to build the persons name into a single string, but all the fields are not always filled in, can anyone tell me the best way to build this so I don't have extra blank spaces when I don't need them?

Basically I need to check if the field has a value, if it does include it, if not leave it out.

Thanks

You have two option use COALESCE or ISNUL in you code, try the link below for details. Hope this helps.

http://hkmicc.wordpress.com/2007/05/25/string-concatenation-in-sql-server/

|||

There are several different ways to handle this, depending upon whether or not you are storing NULL values or empty strings.

Here is a solution if you are storing NULL values:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyContacts table
( RowID int IDENTITY,
Title varchar(10),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(20)
)

INSERT INTO @.MyContacts VALUES ( 'Dr', 'William', 'R', 'Smith' )
INSERT INTO @.MyContacts VALUES ( NULL, 'Bob', NULL, 'Jones' )
INSERT INTO @.MyContacts VALUES ( NULL, 'Mary', 'Anne', 'Williams' )
INSERT INTO @.MyContacts VALUES ( 'Judge', NULL, NULL, 'Wapner' )
INSERT INTO @.MyContacts VALUES ( 'Major', 'Minor', NULL, 'Major' )
INSERT INTO @.MyContacts VALUES ( NULL, 'R', 'P', 'Adams' )

SELECT
PersonsName = isnull((Title + ' '), '' ) +
isnull((FirstName + ' '), '') +
isnull((MiddleName + ' '), '') +
isnull((LastName), '' )
FROM @.MyContacts

PersonsName
-
Dr William R Smith
Bob Jones
Mary Anne Williams
Judge Wapner
Major Minor Major
R P Adams

If you are storing empty strings, you could add a nullif() function around each Field, e.g., isnull((nullif(Title,'') + ' '), '').

|||

Thanks for the replies.

Arnie, your solutions works perfectly, thanks for taking the time.

Regards

Melt