t-sql question - 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)

t-sql question

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2009-02-27 : 16:31:33

I have the following table structure:

col1 col2
abc folder1\folder2\folder3\abc
ttt folder1\folder2\folder3\folder4\ttt
zzz folder1\folder2\folder3\folder4\folder5\zzz
xxx folder1\folder2\xxx

I want to remove the col1 values from the col2 values including the "\" and get the final output.
For example:
1. In the first row, I want to remove \abc from the col2 value and get the final output as folder1\folder2\folder3
2. In the second row, I want to remove \ttt from the col2 value and get the final output as folder1\folder2\folder3\folder4
And likewise..

Please advice, thanks!!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-27 : 16:51:50

Here's one way


declare @t table (col1 varchar(10), col2 varchar(50))
insert @t 
select 'abc', 'folder1\folder2\folder3\abc' union all
select 'ttt', 'folder1\folder2\folder3\folder4\ttt'  union all
select 'zzz', 'folder1\folder2\folder3\folder4\folder5\zzz' union all
select 'xxx', 'folder1\folder2\xxx' union all
select 'NOT_THERE', 'folder1\folder2\xxx' 

select left(col2, nullif(charindex(col1, col2),0) - 2) from @t

OUTPUT:
--------------------------------------------------
folder1\folder2\folder3
folder1\folder2\folder3\folder4
folder1\folder2\folder3\folder4\folder5
folder1\folder2
NULL

Be One with the Optimizer
TG

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-27 : 23:53:57

try this too
select substring(col2,1,len(col2)-charindex('\',reverse(col2))) from @t

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-28 : 00:25:08

Try this once,

declare @t table (col1 varchar(10), col2 varchar(50))
insert @t
select 'abc', 'folder1\folder2\folder3\abc' union all
select 'ttt', 'folder1\folder2\folder3\folder4\ttt' union all
select 'zzz', 'folder1\folder2\folder3\folder4\folder5\zzz' union all
select 'xxx', 'folder1\folder2\xxx'

select col1,case when col2 like '%'+col1+'%' then replace(col2,'\'+col1,'') else NULL end from @t

jbp_j
Starting Member

24 Posts

Posted - 2009-02-28 : 00:47:05

hi,

try this one also.

declare @t table (col1 varchar(10),col2 varchar(50))
insert into @t
select 'abc', 'folder1\folder2\folder3\abc' union all
select 'ttt', 'folder1\folder2\folder3\folder4\ttt' union all
select 'zzz', 'folder1\folder2\folder3\folder4\folder5\zzz' union all
select 'xxx', 'folder1\folder2\xx'

select substring(col2,1,isnull(nullif((patindex('%'+col1+'%',col2)-2 ),-2),len(col2))) from @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources