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)
 Parse file path field

Author  Topic 

wjordan
Starting Member

10 Posts
Posted - 2007-12-19 : 17:08:20
I have a table that stores file paths in the following format "\\root\path1\path2\path3\path4.....and so on. I need to parse this field only to the 4th path and then count how many records are there for "\\root\path1\path2\path3" and of course output this to another table.

Hope this makes since, I've read a lot of good posts on this site and hope you can help me. Be nice this is my first post :-).

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-19 : 17:51:19
[code]-- Prepare sample data
DECLARE @Sample TABLE (Path VARCHAR(260))

INSERT @Sample
SELECT '\\root\path1\path2\path3\path4\File6.xls' UNION ALL
SELECT '\\root\path1\path2\path3\path4\File5.xls' UNION ALL
SELECT '\\root\path1\path2\path4\path4\File4.xls' UNION ALL
SELECT '\\root\path1\pathA\path3\path4\File3.xls' UNION ALL
SELECT '\\root\path10\pathB\path5\path4\File2.xls' UNION ALL
SELECT '\\root\path10\pathB\path5\path4\File1.xls'

-- Show the expected output
SELECT LEFT(Path, Position) AS Path,
COUNT(*) AS Files
FROM (
SELECT Path,
CHARINDEX('\', Path, CHARINDEX('\', Path, CHARINDEX('\', Path, CHARINDEX('\', Path, 3) + 1) + 1) + 1) AS Position
FROM @Sample
WHERE Path LIKE '\\%\%\%\%\%'
) AS d
GROUP BY LEFT(Path, Position)
ORDER BY LEFT(Path, Position)[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-19 : 19:37:08
WOW peso that was quick and good worked like a charm. I knew i came to the right place. Thanks very much
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-19 : 21:39:50
now lets see if I can push my luck :-).
Lets say I wanted to create a stored procedure and pass a variable for the table name, path field, and also wanted to insert the table name as a column before the path and position columns. Would that be pushing it :-).

Thanks for any and all help.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-19 : 21:44:19
Why would you enforce bad database design?
Are you also looking for a parameter to determine the depth of paths to group by?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-19 : 21:51:13
No, this is a third party application that has enforced bad database design and I have been tasked to reorganize the file paths for multiple tables that have links to files on servers. Also the tables dont use the same field names. Once I acquire the correct roots of the files the plan is to reorganize the files and links to a structured root folder. Unfortunately the file links are in multiple tables with different field names. The best I have been able to analyze is that the root folders are 4 deep and then it goes to indiviual folders and files. We are talking about 100's of thousands of files and 6 tables.
Hope this helps you understand my situation.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-19 : 22:01:09
[code]CREATE TABLE #Sample
(
Path VARCHAR(260)
)
GO

INSERT #Sample
SELECT '\\root\path1\path2\path3\path4\File6.xls' UNION ALL
SELECT '\\root\path1\path2\path3\path4\File5.xls' UNION ALL
SELECT '\\root\path1\path2\path4\path4\File4.xls' UNION ALL
SELECT '\\root\path1\pathA\path3\path4\File3.xls' UNION ALL
SELECT '\\root\path10\pathB\path5\path4\File2.xls' UNION ALL
SELECT '\\root\path10\pathB\path5\path4\File1.xls'
GO

CREATE PROCEDURE dbo.uspMyTwitSearch
(
@TableName SYSNAME,
@ColumnName SYSNAME,
@Depth TINYINT
)
AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = '
SELECT LEFT(' + QUOTENAME(@ColumnName) + ', Position) AS Path,
COUNT(*) AS Files
FROM (
SELECT ' + QUOTENAME(@ColumnName) + ',
'

SET @SQL = @SQL + REPLICATE('CHARINDEX(''\'', ' + QUOTENAME(@ColumnName) + ', ', @Depth) + '3)'
SET @SQL = @SQL + REPLICATE(' + 1)', @Depth - 1) + ' AS Position'

SET @SQL = @SQL + '
FROM ' + QUOTENAME(@TableName) + '
WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''\' + REPLICATE('\%', @Depth + 1) + '''
) AS d
GROUP BY LEFT(' + QUOTENAME(@ColumnName) + ', Position)
ORDER BY LEFT(' + QUOTENAME(@ColumnName) + ', Position)'

EXEC(@SQL)
GO

EXEC dbo.uspMyTwitSearch '#Sample', 'Path', 4

DROP PROCEDURE dbo.uspMyTwitSearch
DROP TABLE #Sample[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-20 : 00:24:10
Talking about bad database design, the locations of the path in the tables not only have different field names they have different data types "ntext" and "nvarchar". Is there anyway to get past that.
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-20 : 00:50:25
Oh, btw the tabelname is not getting returned into the result field
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-20 : 04:02:30
[code]CREATE TABLE #Sample
(
Path VARCHAR(260)
)
GO

INSERT #Sample
SELECT '\\root\path1\path2\path3\path4\File6.xls' UNION ALL
SELECT '\\root\path1\path2\path3\path4\File5.xls' UNION ALL
SELECT '\\root\path1\path2\path4\path4\File4.xls' UNION ALL
SELECT '\\root\path1\pathA\path3\path4\File3.xls' UNION ALL
SELECT '\\root\path10\pathB\path5\path4\File2.xls' UNION ALL
SELECT '\\root\path10\pathB\path5\path4\File1.xls'
GO

CREATE PROCEDURE dbo.uspMyTwitSearch
(
@TableName SYSNAME,
@ColumnName SYSNAME,
@Depth TINYINT
)
AS

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = '
SELECT ' + QUOTENAME(@TableName, '''') + ' AS TableName,
LEFT(' + QUOTENAME(@ColumnName) + ', Position) AS Path,
COUNT(*) AS Files
FROM (
SELECT ' + QUOTENAME(@ColumnName) + ',
'

SET @SQL = @SQL + REPLICATE('CHARINDEX(''\'', ' + QUOTENAME(@ColumnName) + ', ', @Depth) + '3)'
SET @SQL = @SQL + REPLICATE(' + 1)', @Depth - 1) + ' AS Position'

SET @SQL = @SQL + '
FROM ' + QUOTENAME(@TableName) + '
WHERE ' + QUOTENAME(@ColumnName) + ' LIKE ''\' + REPLICATE('\%', @Depth + 1) + '''
) AS d
GROUP BY LEFT(' + QUOTENAME(@ColumnName) + ', Position)
ORDER BY LEFT(' + QUOTENAME(@ColumnName) + ', Position)'

EXEC(@SQL)
GO

EXEC dbo.uspMyTwitSearch '#Sample', 'Path', 4

DROP PROCEDURE dbo.uspMyTwitSearch
DROP TABLE #Sample[/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-20 : 10:34:42
You know peso I wish I had of met you a week ago. I know I'm going to wear out my newbie status to this site real quick :-).
I have seen some other posts that might answer this next question. Remember I didn't write the code for this database design!
But to add insult to injury not only did they use "\\" for the beginning of file path they also used things like "C:\" or "T:\".
I'm looking at some posts from Madhi on this subject but I'm up against a time clock so any help would be appreciated.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-20 : 10:37:02
How should C:\ be treated?

C:\Temp

Is that two levels or one level?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-20 : 10:45:02
It is just like the other paths that start with "\\", it may be one folder deep or 15. No one knows, all I'm trying to do right now is just a count of files 4 deep and so far with your help that is what i'm getting. Later comes the hard part of reorganizing these files and moving them ;-). Well except now for this little monkey wrench. Here is what I've visually seen in the tables field, things like "C:\" "T:\" "\" "\\". So pretty much any root configuration you can think of. Thx again.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-20 : 10:53:12
See this
CREATE TABLE	#Sample
		(
			Path VARCHAR(260)
		)
GO

INSERT	#Sample
SELECT	'C:\Windows\Media\path2\path3\path4\File6.xls' UNION ALL
SELECT	'C:\Windows\Media\path2\path3\path4\File6.xls' UNION ALL
SELECT	'\\root\path1\path2\path3\path4\File6.xls' UNION ALL
SELECT	'\\root\path1\path2\path3\path4\File5.xls' UNION ALL
SELECT	'\\root\path1\path2\path4\path4\File4.xls' UNION ALL
SELECT	'\\root\path1\pathA\path3\path4\File3.xls' UNION ALL
SELECT	'\\root\path10\pathB\path5\path4\File2.xls' UNION ALL
SELECT	'\\root\path10\pathB\path5\path4\File1.xls'
GO

CREATE PROCEDURE dbo.uspMyTwitSearch
(
	@TableName SYSNAME,
	@ColumnName SYSNAME,
	@Depth TINYINT
)
AS

SET NOCOUNT ON

DECLARE	@SQL VARCHAR(8000)

SET	@SQL = '
SELECT		' + QUOTENAME(@TableName, '''') + ' AS TableName,
		LEFT(' + QUOTENAME(@ColumnName) + ', Position) AS Path,
		COUNT(*) AS Files
FROM		(
			SELECT	' + QUOTENAME(@ColumnName) + ',
				'

SET	@SQL = @SQL + REPLICATE('CHARINDEX(''\'', ' + QUOTENAME(@ColumnName) + ', ', @Depth) + '3)'
SET	@SQL = @SQL + REPLICATE(' + 1)', @Depth - 1) + ' AS Position'

SET	@SQL = @SQL + '
			FROM	' + QUOTENAME(@TableName) + '
			WHERE	' + QUOTENAME(@ColumnName) + ' LIKE ''\' + REPLICATE('\%', @Depth + 1) + '''
				OR ' + QUOTENAME(@ColumnName) + ' LIKE ''[a-z]:' + REPLICATE('\%', @Depth) + '''
		) AS d
GROUP BY	LEFT(' + QUOTENAME(@ColumnName) + ', Position)
ORDER BY	LEFT(' + QUOTENAME(@ColumnName) + ', Position)'

PRINT @SQL
EXEC(@SQL)
GO

EXEC dbo.uspMyTwitSearch '#Sample', 'Path', 3

DROP PROCEDURE	dbo.uspMyTwitSearch
DROP TABLE	#Sample


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-20 : 11:13:18
Here is the result message I'm getting back, seems the "ntext" field type is the problem, also I don't know if it matters but the possibility exists that the path can be null as well (i.e. blank path). thx so much.

SELECT 'Documents' AS TableName,
LEFT([Location], Position) AS Path,
COUNT(*) AS Files
FROM (
SELECT [Location],
CHARINDEX('\', [Location], CHARINDEX('\', [Location], CHARINDEX('\', [Location], 3) + 1) + 1) AS Position
FROM [Documents]
WHERE [Location] LIKE '\\%\%\%\%'
OR [Location] LIKE '[a-z]:\%\%\%'
) AS d
GROUP BY LEFT([Location], Position)
ORDER BY LEFT([Location], Position)
Msg 8116, Level 16, State 1, Line 2
Argument data type ntext is invalid for argument 1 of left function.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-20 : 11:41:45
Why are you using NTEXT?
Are you expecting paths longer than 260 characters (which is maximum in Microsoft Windows)?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2007-12-20 : 11:43:06
I have replaced LEFT with SUBSTRING for you, which works with NTEXT columns.
CREATE TABLE	#Sample
		(
			[Path] NTEXT
		)
GO

INSERT	#Sample
SELECT	'C:\Windows\Media\path2\path3\path4\File6.xls' UNION ALL
SELECT	'C:\Windows\Media\path2\path3\path4\File6.xls' UNION ALL
SELECT	'\\root\path1\path2\path3\path4\File6.xls' UNION ALL
SELECT	'\\root\path1\path2\path3\path4\File5.xls' UNION ALL
SELECT	'\\root\path1\path2\path4\path4\File4.xls' UNION ALL
SELECT	'\\root\path1\pathA\path3\path4\File3.xls' UNION ALL
SELECT	'\\root\path10\pathB\path5\path4\File2.xls' UNION ALL
SELECT	'\\root\path10\pathB\path5\path4\File1.xls'
GO

CREATE PROCEDURE dbo.uspMyTwitSearch
(
	@TableName SYSNAME,
	@ColumnName SYSNAME,
	@Depth TINYINT
)
AS

SET NOCOUNT ON

DECLARE	@SQL VARCHAR(8000)

SET	@SQL = '
SELECT		' + QUOTENAME(@TableName, '''') + ' AS TableName,
		SUBSTRING(' + QUOTENAME(@ColumnName) + ', 1, Position) AS Path,
		COUNT(*) AS Files
FROM		(
			SELECT	' + QUOTENAME(@ColumnName) + ',
				'

SET	@SQL = @SQL + REPLICATE('CHARINDEX(''\'', ' + QUOTENAME(@ColumnName) + ', ', @Depth) + '3)'
SET	@SQL = @SQL + REPLICATE(' + 1)', @Depth - 1) + ' AS Position'

SET	@SQL = @SQL + '
			FROM	' + QUOTENAME(@TableName) + '
			WHERE	' + QUOTENAME(@ColumnName) + ' LIKE ''\' + REPLICATE('\%', @Depth + 1) + '''
				OR ' + QUOTENAME(@ColumnName) + ' LIKE ''[a-z]:' + REPLICATE('\%', @Depth) + '''
		) AS d
GROUP BY	SUBSTRING(' + QUOTENAME(@ColumnName) + ', 1, Position)
ORDER BY	SUBSTRING(' + QUOTENAME(@ColumnName) + ', 1, Position)'

PRINT @SQL
EXEC(@SQL)
GO

EXEC dbo.uspMyTwitSearch '#Sample', 'Path', 3

DROP PROCEDURE	dbo.uspMyTwitSearch
DROP TABLE	#Sample


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

wjordan
Starting Member

10 Posts
Posted - 2007-12-20 : 13:31:23
SQL Team rocks!!! especially Pero:-), thx very much for the help, worked like a charm.

BTW, pero I missed one of your posts on ntext, as I mentioned earlier I didn't design this database or the front end gui. Which was all done in Access originally and then migrated to SQL, it belongs to some third party company and we support the clients network. I have no idea why anyone would use different data types and different field names in different tables to hold the same kind of data, but they did!!!

There was one interesting thing I noticed, an anomally when executing the script it seemed to have problems handling duplicate subfolders like "\\root\path1\path1\path2". I have no idea why this type of folder structure even exists but it does. but of course this is part of my job, to find this kind of crap and eventually fix it.

But I got the info I needed today and in no small part with your help. Next task will be to find the files with the links and move the files to a new restructed folder path on a nas server and then change the links in the table to point to the new location of the file.

Thx again.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site