Microsoft SQL Server forum - All Roads Lead to Rome but which One is Most Desirable?

Author

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

NickName

2005-07-29, 8:24 pm

-- 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','ghost',
'','bigger')

insert into #cat
values ('Arab0123','Hama','
','Abbas')

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

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

insert into #cat
values ('Mixed003',null,'',
'Smith')

insert into #cat
values ('White123','','','T
alley')

insert into #cat
values ('Yello123','Nick','
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_name,'
') + ' ' + Case Len(middle_name) when 0 then
'' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')
) as
name
from #cat

-- option 2
select (IsNull(first_name,'
') + ' ' +
IsNull(NullIf(Coales
ce((middle_name + ' '),''),''),'') +
IsNull(last_name,'')
) as name
from #cat

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

TIA.

Erland Sommarskog

2005-07-29, 8:24 pm

NickName (dadada@rock.com) writes:
> -- option 1
> select (IsNull(first_name,'
') + ' ' + Case Len(middle_name) when 0 then
> '' else IsNull((middle_name + ' '),'') end + IsNull(last_name,'')
) as
> name
> from #cat
>
> -- option 2
> select (IsNull(first_name,'
') + ' ' +
> IsNull(NullIf(Coales
ce((middle_name + ' '),''),''),'') +
> IsNull(last_name,'')
) as name
> from #cat
>
> q:
> both option 1 and option 2 produces same result, which one is more
> desirable?

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@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

--CELKO--

2005-07-30, 11:23 am

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_name,'
'))
, ' ',' ')
, ' ',' ')
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

2005-08-03, 8:24 pm

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:
> NickName (dadada@rock.com) writes:
>
> 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@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

NickName

2005-08-03, 8:24 pm

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 :)

Sponsored Links