asked on

SQL query to extract a portion of a field

I have a table that contains several folder path strings, down to as many child folders as it may contain.
I want to find a count of each one only down to 'n' level.
So, if I determine I want the parent, and one child, I'd like to return the count for the records where it matches up to that point.
Example:

\parent path\child path 1\
\parent path\child path 1\grandchild path 1
\parent path\child path 1\grandchild path 2\greatgrandchild 1
\parent path\child path 1\grandchild path 3

The above would result in 3 unique 'child' folders within the defined 'parent' (child path 1).
I hope that makes sense, but if not, please ask and I'll provide a better example.

Russ Suter

SQL isn't designed for string manipulation and is therefore not very good at it. However, if you're looking for a count of how many of a particular character appears in a string there is a neat trick that works quite well.

SELECT LEN(PathName) - LEN(REPLACE(PathName, '\', '')) FROM Folders

Open in new window

Máté Farkas

declare @parent_path varchar(1000) = '\parent path\child path 1\'
select distinct left(child, charindex('\', child) - 1) child_path
from (
        select replace(path, @parent_path, '') child
        from YourTable
        where path like @parent_path + '%'
        and len(path) > len(@parent_path)
) p

Open in new window

Brian Crowe

I don't know if this is overkill for what you are trying to do but for fun (it's Monday and I don't feel like doing any real work right now) I thought I'd see what it would take to parse your path's into a table using a recursive cte.

DECLARE @Table TABLE (FilePath VARCHAR(MAX));

INSERT @Table
VALUES
	('\parent path\child path 1\'),
	('\parent path\child path 1\grandchild path 1'),
	('\parent path\child path 1\grandchild path 2\greatgrandchild 1'),
	('\parent path\child path 1\grandchild path 3');

WITH ctePath AS
(
	SELECT FilePath,
		SUBSTRING(FilePath, 2, CHARINDEX('\', FilePath, 2) - 2) AS Folder,
		SUBSTRING(FilePath, CHARINDEX('\', FilePath, 2), LEN(FilePath)) AS Remainder,
		1 AS [Level]
	FROM @Table

	UNION ALL

	SELECT FilePath,
		CASE
			WHEN CHARINDEX('\', Remainder, 2) > 0 THEN SUBSTRING(Remainder, 2, CHARINDEX('\', Remainder, 2) - 2)
			ELSE SUBSTRING(Remainder, 2, LEN(Remainder))
		END AS Folder,
		CASE
			WHEN CHARINDEX('\', Remainder, 2) > 0 THEN SUBSTRING(Remainder, CHARINDEX('\', Remainder, 2), LEN(Remainder))
			ELSE NULL
		END AS Remainder,
		[Level] + 1
	FROM ctePath
	WHERE LEN(Remainder) > 1
)
SELECT FilePath, Folder, [Level],
	COUNT(Folder) OVER(PARTITION BY FilePath) AS FilePathDepth
FROM ctePath
ORDER BY FilePath, [Level]

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sirbounty

ASKER

The first two didn't seem to allow me to repetitively obtain this information for all of my records.
The third which must have been fun, haha, because I don't follow most of it.,:^), may work, but I'm not sure how much it differs from Scott's solution, which does certainly do what I was looking to accomplish (although I also don't seem to understand most of it either).

I can certainly test it if there's a close similarity in a solution there, but I've no way of knowing for certain and not sure how to actually implement it to test?