Help querying field of file paths

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)

Help querying field of file paths

Author

Topic

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2010-01-21 : 16:21:08

I have a column in a database that has full file paths for all files that are located on a file server. I need to be able to provide the users reports to help control file storage. Basic idea is that I need to group by directory so that departments can see what they are using, then group those results by users who can then clean up what they do not need.

Example:
Column Name - Path
z:\Folder1\Folder2\file1.txt
z:\Folder1\Folder2\file2.jpg
z:\Folder2\Folder2\file1.doc

Result I am looking for:

z:\Folder1\Folder2 - 2 Files
z:\Folder2\Folder2 - 1 File

Where it gets tricky is that I have no control over the folder structure so using - group by left(path,18) does not work since I could (and have) a folder structure similar to z:\Folder1\FolderBob\. Any and all help is appreciated!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-21 : 16:33:07

[code]SELECT left(path, len(path) - charindex('\', reverse(path))),
Count(left(path, len(path) - charindex('\', reverse(path)))) numFiles
FROM YOUR_TABLE
GROUP BY
left(path, len(path) - charindex('\', reverse(path)))[/code]

ddobbertin
Yak Posting Veteran

51 Posts

Posted - 2010-01-21 : 16:36:53

Awesome! Exactly what I needed....thanks Russell!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 16:42:58

Here is an approach:

declare @sample table ([path] varchar(255))
insert @sample
select 'z:\Folder1\Folder2\file1.txt' union all
select 'z:\Folder1\Folder2\file2.jpg' union all
select 'z:\Folder2\Folder2\file1.doc' union all
select 'z:\Folder1\FolderBob\file1.png'

select * from @sample

select
reverse(substring(reverse([path]),charindex('\',reverse([path])),255)),
count(*) as [No of files]
from @sample
group by reverse(substring(reverse([path]),charindex('\',reverse([path])),255))
order by reverse(substring(reverse([path]),charindex('\',reverse([path])),255))

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-21 : 16:44:19

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources