February 27, 2009 at 2:23 pm
I have the following table structure:
col1col2
abcfolder1\folder2\folder3\abc
tttfolder1\folder2\folder3\folder4\ttt
zzzfolder1\folder2\folder3\folder4\folder5\zzz
xxxfolder1\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 let me know how I can do this, thanks!!
February 27, 2009 at 2:49 pm
select
col1,
REPLACE(col2 , '\' + col1, '')
from dbo.folderTable
That should do the trick as long as your folder names are not the same as the values in column 1. If they are...it would remove the folder name as well.
February 27, 2009 at 2:54 pm
SELECT REPLACE(col2, '\' + col1, '');
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 2, 2009 at 2:41 pm
If the col1 values are always at the end, so you can do it with the code at the bottom. This same principal be used to remove a file name from a directory path. I assume this is what your objective is, since everything else in the path starts with "folder."
In the SELECT statement, the REVERSE function returns the string of col2's characters read from right to left. This allows the CHARINDEX find the position of the last "\" to appear in the string. Taking the LEFT number of characters from the length of string to the position of the last "\" effectively removes the "\" and the characters following it.
This will not work if the col1 characters appear in the middle of the col2 string, so please let me know if that could ever happen. The solution is slightly longer, but still possible in one set of nested functions.
------------------------------------------------------------------------------------------------
declare @sample table (col1 char(3), col2 varchar(max))
insert into @sample
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, LEFT(col2, LEN(col2) - CHARINDEX('\',REVERSE(col2),1)) as newCol2
FROM @sample
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply