Consider I have a table with a lot of paths like:
How would perform a SELECT query that accurately represents the folder and file structure?
and then querying on the "test" folder should give
Every item in the table knows if it is a directory or a file.
Here's a solution where it is assumed the table has a column indicating whether an item is a folder. Folder items in the table should not end with '\'.
DECLARE @dir varchar(300);
SET @dir = '\test\';
SELECT
dir_item = CASE diritem WHEN '' THEN '.' ELSE dir_item END,
is_folder
FROM (
SELECT
dir_item,
is_folder
FROM (
SELECT
dir_item = STUFF(path_item, 1, LEN(@dir), ''),
is_folder
FROM paths
WHERE SUBSTRING(path, 1, LEN(@dir)) = @dir
) s
WHERE CHARINDEX('\', dir_item) = 0
) s
ORDER BY is_folder DESC, dir_item;
see this blog about convert_anything_to_tree_structures_in_php.
The example covers a similar problem and provides a solution in php.
(doesn't mean you have to do it with php but you may adopt the idea/algorithm)
BW's PHP link would be good to check out, but if you'd like to stick to SQL, try this UDF. Given an input string, it will split it on a specified separator, returning one row per item.
CREATE FUNCTION dbo.Split(@Input VARCHAR(MAX), @Separator CHAR(1))
RETURNS TABLE
AS RETURN
WITH A AS
(
SELECT 1 AS Sequence, CAST(1 AS BIGINT) AS StartChar, CHARINDEX(@Separator, @Input) AS EndChar
UNION ALL
SELECT Sequence + 1, EndChar + 1, CHARINDEX(@Separator, @Input, EndChar + 1)
FROM A
WHERE EndChar > 0
)
SELECT
Sequence,
SUBSTRING(@Input, StartChar, CASE WHEN EndChar = 0 THEN LEN(@Input) - EndChar ELSE EndChar - StartChar END) AS Value
FROM
A
Example: SELECT * FROM dbo.Split('C:\Dir\File.ext', '\') returns:
Sequence Value
1 C:
2 Dir
3 File.ext
This isn't a complete solution, but hopefully it will help!
\folder\subfolder\a,\folder\subfolder\b, and one is a file and the other is a folder, how would you know which one is which?Every item in the table knows if it is a directory or a file.Don't know what you mean, though. Are you talking about someIsDirectorycolumn?