All Roads Lead to Rome but which One is Most Desirable?

[NickName]

-- Business Rule, first name, middle name and last name can all be null
-- ddl
create table #cat (catID char(8) primary key, first_name varchar(15)
null, middle_name varchar(2) null, last_name varchar(15) null)

-- dml, populate sample data
insert into #cat
values ('Black123','gh ost','','bigger ')

insert into #cat
values ('Arab0123','Ha ma','','Abbas')

insert into #cat
values ('Mixed001','', null,null)

insert into #cat
values ('Mixed002',nul l,null,null)

insert into #cat
values ('Mixed003',nul l,'','Smith')

insert into #cat
values ('White123','', '','Talley')

insert into #cat
values ('Yello123','Ni ck','H','Pisa')

-- dml, name concatenation, get all or any
select (first_name + ' ' + middle_name + ' ' + last_name) as name
from #cat
-- the above does not meet with requirement

-- option 1
select (IsNull(first_n ame,'') + ' ' + Case Len(middle_name ) when 0 then
'' else IsNull((middle_ name + ' '),'') end + IsNull(last_nam e,'')) as
name
from #cat

-- option 2
select (IsNull(first_n ame,'') + ' ' +
IsNull(NullIf(C oalesce((middle _name + ' '),''),''),'') +
IsNull(last_nam e,'')) as name
from #cat

q:
both option 1 and option 2 produces same result, which one is more
desirable?

TIA.

[Erland Sommarskog]

Re: All Roads Lead to Rome but which One is Most Desirable?

NickName (dadada@rock.co m) writes:[color=blue]
> -- option 1
> select (IsNull(first_n ame,'') + ' ' + Case Len(middle_name ) when 0 then
> '' else IsNull((middle_ name + ' '),'') end + IsNull(last_nam e,'')) as
> name
> from #cat
>
> -- option 2
> select (IsNull(first_n ame,'') + ' ' +
> IsNull(NullIf(C oalesce((middle _name + ' '),''),''),'') +
> IsNull(last_nam e,'')) as name
> from #cat
>
> q:
> both option 1 and option 2 produces same result, which one is more
> desirable?[/color]

Matter of taste, I guess. This is my favourite, although it gives a
slightly different result:

select CASE WHEN len(first_name) > 0 THEN first_name + ' ' ELSE '' END +
CASE WHEN len(middle_name ) > 0 THEN middle_name + ' ' ELSE '' END +
CASE WHEN len(last_name) > 0 THEN last_name ELSE '' END
name
from #cat



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[--CELKO--]

Re: All Roads Lead to Rome but which One is Most Desirable?

Another way that will clean out spaces and reduce the NULL name strings
to a single space

SELECT REPLACE(
REPLACE(
(COALESCE(first _name,' ')
+ COALESCE(middle _name, ' ')
+ COALESCE(last_n ame,' '))
, ' ',' ')
, ' ',' ')
AS full_name
FROM Foobar;

You can also have some control over patterns of NULLs

SELECT COALESCE(
(last_name + ', ' + first_name + ' ' + middle_name),
(last_name + ', ' + first_name),
('{no lastname}, ' + first_name + ' ' + middle_name),
('{no lastname}, ' + first_name),
etc.
('{no name provided}') - all nulls
) AS full_name
FROM Foobar;

But you really ought to be doing display in the front end and not the
database

[NickName]

Re: All Roads Lead to Rome but which One is Most Desirable?

Thanks, Erland, sorry it took me so long to respond (lived in the dark
age for last coule of days -- not getting on the net)

Erland Sommarskog wrote:[color=blue]
> NickName (dadada@rock.co m) writes:[color=green]
> > -- option 1
> > select (IsNull(first_n ame,'') + ' ' + Case Len(middle_name ) when 0 then
> > '' else IsNull((middle_ name + ' '),'') end + IsNull(last_nam e,'')) as
> > name
> > from #cat
> >
> > -- option 2
> > select (IsNull(first_n ame,'') + ' ' +
> > IsNull(NullIf(C oalesce((middle _name + ' '),''),''),'') +
> > IsNull(last_nam e,'')) as name
> > from #cat
> >
> > q:
> > both option 1 and option 2 produces same result, which one is more
> > desirable?[/color]
>
> Matter of taste, I guess. This is my favourite, although it gives a
> slightly different result:
>
> select CASE WHEN len(first_name) > 0 THEN first_name + ' ' ELSE '' END +
> CASE WHEN len(middle_name ) > 0 THEN middle_name + ' ' ELSE '' END +
> CASE WHEN len(last_name) > 0 THEN last_name ELSE '' END
> name
> from #cat
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[NickName]

Re: All Roads Lead to Rome but which One is Most Desirable?

Ahe, the REPLACE function is real nice, thanks, Mr. Celko. With regard
to "But you really ought to be doing display in the front end and not
the
database", I'm doing for it the front guy/gal to make her job easier :)