February 15, 2017 at 8:02 am
HI
Hope someone is able to help. I'm very new to SQL (6months) and need to split one column in a database into multiple columns. There are a few database I need to do this on. The each database column contains UNC paths separated by '\' (backslash), the columns vary in length and the number of backslashes.The database contains a list of files and their locations on the network.
Example data
Database1
Filename Folder
document1.pdf June\Newsletters\London\
document2.pdf July\Newsletters\Birmingham\
Database2
document3.xls DomainA\ServerA\ShareA\
Document4.xls DomainA\ServerB\ShareA\
document4.doc DomainB\Server\ShareC\JoeBloggs\
For each database I would like to name the columns differently. I just want to be able to split the name in the Folder column into mutiple columns
Desired Output
Database1
Filename Month Type Site
document1.pdf June Newsletters London
document2.pdf July Newsletters Birmingham
And
Database2
Filename Site Server Share User
document3.xls DomainA ServerA ShareA NULL
Document4.xls DomainA ServerB ShareA NULL
document4.doc DomainB Server ShareC JoeBloggs
Many in advance..
February 15, 2017 at 8:32 am
I guess it depends on what you're looking to accomplish, exactly. I don't have an entirely clear picture of what you need to do beyond your need to split a string that contains a file path as well as the file name. You can quite easily do that using Jeff Moden's string splitter function, but that will give you separate records to deal with. For example, if you're file path and file name were as follows:
\\SERVERNAME\SHARENAME\Folder\Subfolder\Filename.ext
Then using the DelimitedSplit8K function would look like this:
DECLARE @FILE_PATH AS varchar(100) = '\\SERVERNAME\SHARENAME\Folder\Subfolder\Filename.ext';
SELECT S.Item, S.ItemNumber - 2 AS ItemNumber
FROM dbo.DelimitedSplit8K(@FILE_PATH, '\') AS S
WHERE S.ItemNumber > 2
ORDER BY S.ItemNumber;
The results would look like this:
Item ItemNumber
SERVERNAME 1
SHARENAME 2
Folder 3
Subfolder 4
Filename.ext 5
Then the question is, what do you want to do with that information? If you need to flatten it out into a single set of fields, that's a bit more complicated, but certainly not impossible... Just needs a little T-SQL rocket science... FOR XML PATH('') is coming to mind, but there may be other methods.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 16, 2017 at 2:52 am
Thanks Steve for replying
Basically what I have is a database that contains a list of files, with columns
filename
filesize,
dateadded
folderpath (their location on the network).
There are over 1,000,000 records. It is a report of a list of files on a network share mostly histroic but required to be kept for legal reason. They are stored in folders which are named according to certain categories which is good. But there are duplicate categories, duplicate file names etc abit of a jumble really. I need to find a way to differentiate the categories etc so i can audit and keep track of these files. It is something my precedessor started or promised the business before leaving. The only thing in my handover was the logical way would be to split the folderpath into its parts. Such as \Wales\ServerA\CategoryA\CategoryB into
etc.
Where the first category are the same length i tried substrings. But i've only tested this on some test data, it went astray when when it hit category longer than say 5.
February 16, 2017 at 4:21 am
Is there the possibility that a user could have further sub folders in their share? For example:
DomainB\Server\ShareC\JoeBloggs\Financials\Sampledocument.ext
What would happen here if so?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 16, 2017 at 4:27 am
Thanks Thom for the heads up. I will go through the link and probably re-post. Yes there are multiple subfolders, some files being 8 folders deep some just 2.
February 16, 2017 at 4:30 am
suki.rai 97120 - Thursday, February 16, 2017 4:27 AMThanks Thom for the heads up. I will go through the link and probably re-post. Yes there are multiple subfolders, some files being 8 folders deep some just 2.
So how would you want that displayed in both environments?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 16, 2017 at 4:34 am
Ideally what I would like is if its 8 folders deep then 8 columns if just two folders deep then two columns.
February 16, 2017 at 4:49 am
suki.rai 97120 - Thursday, February 16, 2017 4:34 AMIdeally what I would like is if its 8 folders deep then 8 columns if just two folders deep then two columns.
Is this a table your storing in, or displaying the data in a view then? Either way it's breaking first normal form, but if you have an indeterminate amount of columns, then there's no way you could create a table to store it. if you made it so that you can have up to 8 layers, and then someone comes along with 12, the entire system breaks down. To make a View you'd likely need to use Dynamic SQL and a PIVOT. Neither are good ideas (the first is awful, the second is almost as bad).
Out of interest, why do you want to store the data like this. Why not use a presentation layer to display it in the format you want, and have SQL store the data properly?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 16, 2017 at 12:32 pm
As Thom has already said, this approach violates First Normal Form, and you've already run into problems because of this. You should reconsider your approach.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply