length value in row - 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 2005 Forums

Transact-SQL (2005)

length value in row

Author

Topic

nrsh_ram
Starting Member

3 Posts

Posted - 2010-06-03 : 22:57:16

hi to all...

im newbie for SQL...
here i have one row called Coordinator..

Coordinator..value is
PROJECT\a;PROJECT\b;PROJECT\c;PROJECT\e;PROJECT\d
PROJECT\a
PROJECT\a;PROJECT\b
PROJECT\d
PROJECT\e

i want the data of Coordinator like this
PROJECT\e
PROJECT\d
PROJECT\a

i dont want read the others..
how to do it???

plz help me..

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-04 : 01:17:53

Try this:

Declare @T Table(
Tval varchar(5000))

Insert into @T
Select 'PROJECT\a;PROJECT\b;PROJECT\c;PROJECT\e;PROJECT\d' union all
Select 'PROJECT\a' union all
Select 'PROJECT\a;PROJECT\b' union all
Select 'PROJECT\d' union all
Select 'PROJECT\e'

select distinct
Case When Charindex(';',Tval)>0 then substring(Tval,1, Charindex(';',Tval)-1)
else Tval
end
From @T

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 03:46:29

or

select distinct substring(Tval,1,patindex('%\[a-zA-Z]%',Tval)+1) from @T

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources