parsename function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

parsename function

Author

Topic

cardgunner

326 Posts

Posted - 2008-09-18 : 15:36:43

I shopuld be excited that the code below works but can is there a cleaner way to do it.

select 	t_nama,
case 	when t_nama  like '%.%' then 
		ltrim(parsename(rtrim(t_nama), 1) )+ ', ' +parsename(t_nama, 2) 
	else  isnull(parsename(replace(t_nama,' ', '.'), 1)
		 + ', ' +parsename(replace(t_nama,' ', '.'), 2)
		 + ' ' + parsename(replace(t_nama,' ', '.'),3),parsename(replace(t_nama,' ', '.'), 1)
		 + ', ' +parsename(replace(t_nama,' ', '.'), 2)) end t_fnam
from
(
select 'house account' t_nama union all
select 'truck house account' union all
select 'part. account'
)	tmp 
results

t_nama              t_fnam                                                     
------------------- ---------------------- 
house account       account, house
truck house account account, house truck
part. account       account, part

As I said the code of above works but can it be done with less code?

CardGunner

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-18 : 16:16:22

There are always alternatives to what you have - they may appear cleaner and they may perform better. If it is for asthetics then I'd say forget about it. But the right way to do it is to store the individual parts in their own columns. You can always assemble the parts anyway you want from there. That is the "cleaner way to do it".

Be One with the Optimizer
TG

cardgunner

326 Posts

Posted - 2008-09-18 : 16:26:34

quote:
Originally posted by TG

But the right way to do it is to store the individual parts in their own columns.

Amen to that!

I wish they were. But I can't do much with the front end "it is what it is"

Thanks for the reply. I just didn't like the way it looked.

If it looks like a dog, if it barks like a dog, if it smells like a dog it must be a fish.

CardGunner

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-19 : 03:15:58

Not sure why you need this. Here is other approach

select 
	right(t_nama,charindex(' ',reverse(t_nama))-1)+','+ 
	left(t_nama,len(t_nama)-charindex(' ',reverse(t_nama)))
from
(
select 'house account' t_nama union all
select 'truck house account' union all
select 'part. account'
) tmp

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources