String processing during table creation and inserting from another table

Question

I have a table that contains file paths, like so:

--------------------
|Files             |
--------------------
|path nvarchar(500)|
--------------------

I want to split it into two tables, one containing unique directories and one containing filenames:

---------------------------
|Files                    |
---------------------------
|filename    nvarchar(255)|
|directoryId int          |
---------------------------

---------------------------
|Directories              |
---------------------------
|id          int          |
|path        nvarchar(255)|
---------------------------

So for example if an entry originally was "C:/folder/file.jpg", I want an entry in Directories for "C:/folder/", and the entry in Files would be updated to have "file.jpg" for the filename and the directory id of the new entry in Directories.

(In case you're wondering at this point, the reason I need to do this is because I need to keep track of some information at the directory level.)

Is there a good way to do this in a T SQL script?

Answer 1

try this:

declare @filename varchar(500)

set @filename = 'C:/Folder/file.jpg'

select right(@filename, charindex('/',reverse(@filename))-1)

select left(@filename, len(@filename) - charindex('/',reverse(@filename))+1)

This will be the whole conversion:

insert into Directories ([path])
select distinct
    left([path], len([path]) - charindex('/',reverse([path]))+1) as [path]
from files

select 
    d.id,
    right(f.[path], charindex('/',reverse(f.[path]))-1)
from files f
inner join directories d
    on left(f.[path], len(f.[path]) - charindex('/',reverse(f.[path]))+1) = d.[path]

I was not sure the real names of the tables because you have two Files tables in your schema.

Answer 2

DECLARE @FULL VARCHAR(50)
DECLARE @PATH VARCHAR(50)
DECLARE @FILE VARCHAR(50)
DECLARE @directoryId INT

DECLARE curs CURSOR FAST_FORWARD FOR
SELECT path FROM FILES_1

OPEN curs
FETCH NEXT FROM curs
INTO @FULL

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FILE = right(@FULL, charindex('/',reverse(@FULL))-1)
    SET @PATH = left(@FULL, len(@FULL) - charindex('/',reverse(@FULL))+1)
    SET @directoryId = SELECT ID from Directories WHERE  path = @PATH

    BEGIN TRANSACTION

        IF @directoryId IS NULL
        BEGIN
            INSERT INTO Directories VALUES (@PATH)
            SET @directoryId = @@IDENTITY
        END

        INSERT INTO  Files VALUES ( @FILE,@directoryId)

        IF @@ERROR <> 0 ROLLBACK

    COMMIT TRANSACTION
END

CLOSE curs
DEALLOCATE curs

Changed to a cursor!