[RESOLVED] SQL Server String Concatenation with Possible Nulls

[MMock]

Here's my query so far:

SELECT 
	ISNULL(tblHealthCareProviders.HCPPrefix, '') + 
	 tblHealthCareProviders.HCPFName + 
	 ' ' +
	 tblHealthCareProviders.HCPLName + 
	 ISNULL(tblHealthCareProviders.HCPSuffix, ''), etc

What I am doing is creating a single name field out of four possible data in a table: prefix (Dr., Mrs., etc) first name, last name and suffix (Jr., Sr., etc). This query is too simple because what I need to do is: if there is a suffix, append a comma and a space to last name and then append the suffix, otherwise just leave last name alone.

So I would get John Berry, Jr. instead of John BerryJr which is what the query above is giving me.

Do I have to break this out and code something more complex than ISNULL can handle?

Thanks.

[techgnome]

case....

select
  case when tblHealthCareProviders.HCPPrefix is not null then tblHealthCareProviders.HCPPrefix + ' ' else '' end + 
  tblHealthCareProviders.HCPFName + 
    ' ' +
  tblHealthCareProviders.HCPLName +
  case when tblHealthCareProviders.HCPSuffix is not null then ', ' + tblHealthCareProviders.HCPSuffix else '' end as FULLNAME
....

-tg

[szlamany]

I've done this in a UDF

CREATE FUNCTION dbo.GetFullName_F (@LastName as varchar(35),@FirstName as varchar(20),@MiddleName as varchar(20),@Suffix as varchar(10),@Opt as int)
RETURNS varchar(100) AS  
BEGIN
	Declare @WN varchar(100)

	Set @WN=IsNull(@LastName,'')+', '+IsNull(@FirstName,'')
		+Case When IsNull(@MiddleName,'')<>'' Then ' '+@MiddleName Else '' End
		+Case When IsNull(@Suffix,'')<>'' Then ', '+@Suffix Else '' End

	RETURN @WN
END

[techgnome]

Only thing missing is the title/prefix, but that's easily added in...

-tg

[szlamany]

This won't work

tblHealthCareProviders.HCPSuffix is not null

if the field contains blanks...

[MMock]

This won't work

tblHealthCareProviders.HCPSuffix is not null

if the field contains blanks...

I cannot test that as the field does not contain blanks, but that does not mean it won't. So thanks for catching that.

[szlamany]

I only use FIELD IS NULL or FIELD IS NOT NULL when testing for date columns

Otherwise I feel safer using

IsNull(FIELD,'')<>''

[MMock]

Before I saw szlamany's post, I had worked up to this:

SELECT Name = 
	CASE  
		WHEN HCPSuffix IS NULL OR HCPSuffix = '' THEN
			ISNULL(tblHealthCareProviders.HCPPrefix, '') + 
				 tblHealthCareProviders.HCPFName + 
				 ' ' +
				 tblHealthCareProviders.HCPLName 
		ELSE
			ISNULL(tblHealthCareProviders.HCPPrefix, '') + 
				 tblHealthCareProviders.HCPFName + 
				 ' ' +
				 tblHealthCareProviders.HCPLName + 
				 ', ' +  tblHealthCareProviders.HCPSuffix
		END
FROM tblHealthCareProviders

to take care of blanks in the suffix field.

I should modify this to IsNull(FIELD,'')<>" - yes?

[techgnome]

depends on the context... I have several places where blank and NULL are two very distinctly different meanings ... NULL being what it is supposed to mean: unknown... while a blank value is a valid value and needs to be treated as such.

-tg

[szlamany]

...I should modify this to IsNull(FIELD,'')<>" - yes?

I would for this specific situation...

[MMock]

But in this case, blank and NULL I believe would both signify "there is no suffix". And like I said, right now suffix is never blank. and I just saw that there's close to 500,000 rows in the table. That's a pretty good sampling!

[szlamany]

I am a bit of a purist - and I just don't like this code

HCPSuffix IS NULL OR HCPSuffix = ''

for one single reason.

If a field can possibly contain nulls then it should never - imo - be used in an = or <> situation without IsNull() function.

Null can NEVER equal anything.

Granted your OR statement protects you - but I still can't get around the invalid statement of HCPSuffix = ''.

What if someone comes along later and makes the FORMULA more complex - adding some more OR's - or simply removes the HCPSuffix is null?

The only reason the HCPSuffix = '' works is because you have "sister-code" sitting next to it protecting it from itself.

My two cents!

btw - you still working in CT?

[MMock]

I appreciate your insistence. And the fact is, we are writing this code for a client and then they are buying the source for maintenance on their own (not usually how we do things here) so it really should be perfect.

Yes - Stratford.

[szlamany]

Shelton - wow - small world - and I do health care software for insurance companies - how coincidental!

[MMock]

That is funny! Before, I was in West Hartford then Bloomfield. I've been here a year and it's permanent so I hope to stay a long time. This just happens to be healthcare - TLBB. We do a variety of sites, like Hilton Hotels, Owens Corning, Invisalign (braces), etc.

[szlamany]

Health care is just a small part of what I do - but mostly work for not-for-profit like firms - unions, school districts - towns and such.

TLBB? bb as in blue cross/blue shield?

[MMock]

"The Little Blue Book". It's a directory of practices and providers. I can't remember where the client is, but they are in CT too.

[MMock]

Now I've probably said too much and will get fired for breach of confidentiality...

[szlamany]

Remove it if you feel you need to - the google spiders will find it in the next few minutes!!

[MMock]

Are you joking? Because I was. But I am learning to be paranoid and am getting better at it. How do you remove a post?

[MMock]

Actually, I just googled it, and TLBB is an acronym for many things, and The Little Blue Book also has several definitions. Wikipedia has a completely different description of it.

Anyway, this is what I ended up with:

SELECT 

  CASE 
  -- Build "Name" out of the prefix, first name, last name and suffix, checking for nulls in the prefix and suffix parts. 
  
    -- Note, this: IsNull(FIELD,'')<>'' is a safer way of checking for nulls in FIELD than a direct ISNULL test.
	WHEN ISNULL(tblHealthCareProviders.HCPPrefix,'')<>'' THEN 
		tblHealthCareProviders.HCPPrefix + ' ' 
	ELSE '' 
	
	END 
	
	+ tblHealthCareProviders.HCPFName 
	+ ' ' 
	+  tblHealthCareProviders.HCPLName 
	+
  
	  CASE 
		WHEN ISNULL(tblHealthCareProviders.HCPSuffix,'')<>'' THEN 
			', ' + tblHealthCareProviders.HCPSuffix 
		ELSE '' 
		END 
				
	AS Name, 
etc (omitted, to protect the innocent)

Thanks.

[techgnome]

"but mostly work for not-for-profit like firms " - Crap! Srsly? I need to stop helping you then.... that's our target too...

-tg

[szlamany]

@tg - I tried to PM you - I guess u don't take them !

PM me with "What company do you work for?"

[MMock]

You guys - whatever happened to good, old-fashioned email???

[szlamany]

I got 143 spam emails yesterday alone - these are ones that my COMCAST BUSINESS CLASS provider and OUTLOOK didn't already put to junk mail...

I try to limit the use of my email as much as possible - just to my actual clients!

[MMock]

Oh, I see.

You can use the VBForums mail. That's what I use to email other members here.

[szlamany]

Wow - I thought PM was the only forum method - didn't really know about vbforum email...

[MMock]

Ha - I just sent you one.