October 26, 2015 at 11:28 am
I have a problem where I need to select all top level file paths from a string value in SQL
So I have a column "Locations"
Example Data:
X:\folder\anotherfolder\
X:\folder\yet another folder\
X:\foldername\another folder\
X:\foldername\yet another folder\
I'd want to return only:
X:\folder\
X:\foldername\
So I am guessing I need to somehow parse the sting and capture anything before the second '\'?
Any help would be greatly appreciated!
AMO AMAS AMATIT AGAIN
October 26, 2015 at 11:35 am
here's one way to do it:
since i want to find the second slash, i know i can use charindex to start at char 4 in your path.
/*
X:\folder\anotherfolder\X:\folderX:\folder\yet another folder\X:\folderX:\foldername\another folder\X:\foldernameX:\foldername\yet another folder\X:\foldername*/
;WITH MyCTE([Locations])
AS
(
SELECT 'X:\folder\anotherfolder\' UNION ALL
SELECT 'X:\folder\yet another folder\' UNION ALL
SELECT 'X:\foldername\another folder\' UNION ALL
SELECT 'X:\foldername\yet another folder\'
)
SELECT *,SUBSTRING([Locations],1,CHARINDEX('\', [Locations],4)) As TopFolder FROM MyCTE;
Lowell
October 26, 2015 at 11:44 am
DECLARE @PATH TABLE (Locations VARCHAR(260));
INSERT @PATH
VALUES ('X:\folder\anotherfolder\'),
('X:\folder\yet another folder\'),
('X:\foldername\another folder\'),
('X:\foldername\yet another folder\');
SELECT DISTINCT LEFT(Locations, CHARINDEX('\', Locations, 4)) AS 'Path'
FROM @PATH;
October 26, 2015 at 12:41 pm
I tried this agains all 54,000+ rows and it returned correctly in seconds! Thanks so much!
AMO AMAS AMATIT AGAIN
October 26, 2015 at 12:42 pm
Thank-you for your time everyone!
AMO AMAS AMATIT AGAIN
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply