[RESOLVED] SQL Server String Concatenation with Possible Nulls

Here's my query so far:

Code:

---

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.

case....

Code:

---

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

I've done this in a UDF

Code:

---

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

---

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

-tg

This won't work

tblHealthCareProviders.HCPSuffix is not null

if the field contains blanks...

Quote:

---

Originally Posted by szlamany

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.

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

Otherwise I feel safer using

IsNull(FIELD,'')<>''

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

Code:

---

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?

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

Quote:

---

Originally Posted by MMock

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

---

I would for this specific situation...

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!

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?

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.

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

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.

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?

"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.

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

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

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?

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:

Code:

---

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.

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

-tg

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

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

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

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!

Oh, I see.

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

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

Ha - I just sent you one.