June 30, 2011 at 11:48 am
Good afternoon,
I need to create some reports. I have a table that has a field called path. The path column is a varchar(2000) not null.
The developers created a java app to scan a server and put in the files that were on the server. This
I need to create a report to monitor the backup shares to see how many backup files are under each backup share. There should be 7 files. One file for each day. After 7 days, the maintenance plan should have deleted older files.
An example of data being loaded into the path column is this:
\\thisServer.account.ourdomain.net\drive$\ShareFolder\SubFolder1\SubFolder2\SubFolder3\Filename.bak
okay....
Now I need to write some TSQL to that returns a Column named SharedFolder and then count group the resultset by that Column ("SharedFolder").
To do that, I need to write some TSQL to return \\thisServer.account.ourdomain.net\drive$\ShareFolder as Shared folder.
My guess at the TSQL is use the substring function as follows:
substring(path,startingPoint,LengthOfCharacters) = '\\thisServer.account.ourdomain.net\drive$\ShareFolder'
1) The only way I can think to do this is find the fourth occurrence position of '\' and use it as a starting point.
2) Then find the fifth occurrence position of '\'.
3) Subtract the fourth occurrence position from the fifth occurrence position to get the length of characters.
I found some code to find the nth occurrence of a character:
LEN(path) - LEN(REPLACE(path, '\', ''))
But it doesn't return the position of the nth occurrence.
Does anyone have an idea of how to find the nth occurrence of a character in a string?
Thanks,
Tony
Note: The table in the above is in a database on a SQL Server 2005 server (SRVC PK 2). Our SSRS server is SQL Server 2008 R2.
Things will work out. Get back up, change some parameters and recode.
June 30, 2011 at 12:27 pm
Finding the n-th instance of a character in a string is greatly simplified if you use a Tally table. Here is a script to create one in your database if you don't have one already:
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/[/url]
Once you have a Tally table you can do neat things like this:
IF OBJECT_ID(N'tempdb..#tmp') > 0
DROP TABLE #tmp ;
GO
CREATE TABLE #tmp
(
share_name VARCHAR(500)
) ;
GO
INSERT INTO #tmp
(
share_name
)
VALUES (
'\\thisServer.account.ourdomain.net\drive$\ShareFolder\SubFolder1\SubFolder2\SubFolder3\Filename.bak'
) ;
GO
WITH slashes(share_name, position, slash_instance)
AS (
SELECT shares.share_name,
N,
ROW_NUMBER() OVER (PARTITION BY share_name ORDER BY N)
FROM dbo.Tally t
CROSS JOIN #tmp shares
WHERE SUBSTRING(shares.share_name, N, 1) = '\'
),
slash_positions(share_name, position_4, position_5)
AS (
SELECT share_name,
MAX(CASE WHEN slash_instance = 5 THEN position
END),
MAX(CASE WHEN slash_instance = 6 THEN position
END)
FROM slashes
GROUP BY share_name
)
SELECT share_name,
SUBSTRING(share_name, position_4 + 1, position_5 - position_4 - 1) AS directoy_name
FROM slash_positions ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 2:46 pm
Wow!!!
I am very impressed and very thankful for your help!!
Thanks.
I had to spend some time working through your example:
1) I had to create the tally table.
2) I don't use CTEs so I had to get up to speed on them.
3) Then I was able to go through your example.
It worked.
I am off and running on building this much needed report.
Thanks again.
Tony
Things will work out. Get back up, change some parameters and recode.
June 30, 2011 at 2:49 pm
Excellent, you're very welcome 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 30, 2011 at 9:00 pm
WebTechie38 (6/30/2011)
Wow!!!I am very impressed and very thankful for your help!!
Thanks.
I had to spend some time working through your example:
1) I had to create the tally table.
2) I don't use CTEs so I had to get up to speed on them.
3) Then I was able to go through your example.
It worked.
I am off and running on building this much needed report.
Thanks again.
Tony
Tony, it sounds like you may not have heard of a "Tally" or "Numbers" table before. As you can see, they can make some very complex things a whole lot easier. If you know how a "Tally" Table works, you might be able to use it to your advantage in the future. The following link will take you to an article that explains what a Tally Table is, how it works, and gives just a couple examples of the many things you can do with it.
http://www.sqlservercentral.com/articles/T-SQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2011 at 9:12 pm
Tony,
One more "extension" of the problem you posed. It's super easy to break out all of the individual parts of the path and number them. Shamelessly borrowing from Orlando's test data...
IF OBJECT_ID(N'tempdb..#tmp') > 0
DROP TABLE #tmp ;
GO
CREATE TABLE #tmp
(
share_name VARCHAR(500)
) ;
GO
INSERT INTO #tmp
(
share_name
)
SELECT '\\thisServer.account.ourdomain.net\drive$\ShareFolder\SubFolder1\SubFolder2\SubFolder3\Filename.bak' UNION ALL
SELECT '\\thisServer.account.ourdomain.net\drive$\ShareFolder\SubFolderA\SubFolder2\SubFolder3\Filename.bak' UNION ALL
SELECT '\\thisServer.account.ourdomain.net\drive$\ShareFolder\SubFolderB\SubFolder2\SubFolder3\Filename.bak'
GO
Here's some code that will split out each "element" of the path according to its position:
SELECT tmp.share_name, split.ItemNumber, split.Item
FROM #tmp tmp
CROSS APPLY dbo.DelimitedSplit8K(tmp.share_name,'\') split
;
You can get the dbo.DelimitedSplit8K function from "Resources" link at the bottom of the following URL:
http://www.sqlservercentral.com/articles/Tally+Table/72993/
And, yes, it too uses a type of "Tally Table". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2011 at 11:28 pm
Jeff Moden (6/30/2011)
One more "extension" of the problem you posed.
Did you forget to post the splitting code?
July 1, 2011 at 6:47 am
SQLkiwi (6/30/2011)
Jeff Moden (6/30/2011)
One more "extension" of the problem you posed.Did you forget to post the splitting code?
I didn't post the code but, if you look under the code window I posted, there's a link. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2011 at 8:35 am
Jeff Moden (6/30/2011)
Tony,One more "extension" of the problem you posed. It's super easy to break out all of the individual parts of the path and number them. Shamelessly borrowing from Orlando's test data...
Shamelessly borrowing :laugh:
I have you to thank for showing me the light regarding a numbers tables in the first place Jeff! I was hoping you would drop by this thread. I suspect you have forgotten more than I know about set-based processing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 1, 2011 at 9:26 am
Jeff Moden (7/1/2011)
I didn't post the code but, if you look under the code window I posted, there's a link. 🙂
I was expecting the code you posted to use the split function. As it is it just loads some sample data, unless I'm missing something crucial...?
July 2, 2011 at 1:55 pm
SQLkiwi (7/1/2011)
Jeff Moden (7/1/2011)
I didn't post the code but, if you look under the code window I posted, there's a link. 🙂I was expecting the code you posted to use the split function. As it is it just loads some sample data, unless I'm missing something crucial...?
Ah... I see. :blush: Thanks, Paul. I don't know what happened to the second code window that I'm sure I posted (or not so sure, now). I'll be back in a few with that code.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2011 at 3:13 pm
I've modified the post (se the second code box) were I was missing the USE of the split function. Apologies for the previous omission.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply