On Jul 9, 3:18 pm, Fieldmedic <Fieldme...@discussions.microsoft.com>
wrote:

Can you run it like this and avoid the CASE:

patient.first + ISNULL(patient.middle,'') + patient.last

WHEN NULL doesn't work the way you want it too.  NULL is never equal
to NULL, oddly enough.

One alternative:

patient_name = patient.first +
              COALESCE(' ' + patient.middle + ' ', ' ') +
              patient.last

Another:

patient_name = case
    when patient.middle IS NULL
    then patient.first+' '+patient.last
    else patient.first+' '+patient.middle+' '+patient.last
    end,

Roy Harvey
Beacon Falls, CT

No need for CASE here at all, I think...

SELECT COALESCE(patient.first, '') + COALESCE(' ' + patient.middle, '') +
COALESCE(' ' + patient.last, '')
FROM table

On 7/9/08 3:18 PM, in article
8ADB5200-AE2F-463E-BAC6-591A67182F0B@microsoft.com, "Fieldmedic"

I suspect the way you coded it SQL is doing an equal compare and of course
nothing including a null ever equals another null. I coded it like you and
with a little variation. The variation works.

DECLARE @Firstname    VARCHAR(255);
DECLARE @Middlename    VARCHAR(255);
DECLARE @Lastname    VARCHAR(255);

SELECT    @Firstname    =    'George'
        ,@Middlename    =    NULL
        ,@Lastname    =    'Washington';
--    returns null
SELECT    CASE    @Middlename
        WHEN    NULL    THEN    @Firstname + ' ' +     @Lastname
        ELSE    @Firstname + ' ' + @Middlename  + ' ' +    @Lastname
        END;
-- returns what you want
SELECT    CASE    WHEN @Middlename IS NULL
        THEN    @Firstname + ' ' +     @Lastname
        ELSE    @Firstname + ' ' + @Middlename  + ' ' +    @Lastname
        END;