VinnyGuitara VinnyGuitara - 3 months ago 12
SQL Question

How to use sql to find parent directoryId

I have some data in a giant table that looks like this:

directoryId Length Directory
7788 631 Y:\Foo
3606 70246 Y:\Foo\Bar


How can I use sql to find the parent folder and provide the parent id?

DirID ParentDirId Length Directory
3606 7788 70246 Y:\Foo\Bar


Source Tables:

CREATE TABLE [Datamap].[SourceFiles](
[DirectoryID] int IDENTITY(1,1),
[Length] [float] NULL,
[Directory] [nvarchar](255) NULL,
[Extension] [nvarchar](255) NULL,
[Type] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


I only have a direcotry ID on every row.

So I edited this and my solution. I have created one table as shown above and it contains data like this:

directoryId Length Directory Extension Type
23572 2270 Y:\Data\FS02-V\HarvardPilgrim\Development\Dory\FHCP ICD10 sql Sql Scripts
23572 4396 Y:\Data\FS02-V\HarvardPilgrim\Development\Dory\FHCP ICD10 sql Sql Scripts
23572 1420 Y:\Data\FS02-V\HarvardPilgrim\Development\Dory\FHCP ICD10 sql Sql Scripts

Answer

Here is one possible solution.

Declare @YourTable table (directoryId int,Length int, Directory varchar(250))
Insert into @YourTable values
(7788,631,'Y:\Foo'),
(3606,70246,'Y:\Foo\Bar'),
(3607,70246,'Y:\Foo\Bar\SomeDir & Date')



;with cteBase as (
Select A.*
      ,B.*
 From  @YourTable A
 Cross Apply (Select Top 1 * from [dbo].[udf-Str-Parse](Replace(A.Directory,'&','&'),'\') Order By Key_PS Desc) B
)
Select A.directoryId
      ,ParentDirId   =B.directoryId
      ,A.Length
      ,A.Directory
  From cteBase A
  Left Join cteBase B
    on (B.Directory+'\'+Replace(A.Key_Value,'&','&') = Replace(A.Directory,'&','&'))

Returns

directoryId ParentDirId Length  Directory
7788        NULL        631     Y:\Foo
3606        7788        70246   Y:\Foo\Bar
3607        3606        70246   Y:\Foo\Bar\SomeDir & Date

The Parser UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End