CODEWITHSUNDEEP
sqldatabaseoracle-databaseunixpath
artbarzz
artbarzz

Reputation: 93

Full Path Database Query

Hello I am trying to solve this problem

Given a database table such as: create table file(id int, parentid int, name varchar(1024), size int, type char(1)); write a single (recursive) database query to list FULL PATH of all files. [assume type is either "F" or "D" for file or directory]. Your query should give you similar output to unix command: "find . -type f".

This is what I've got so far but I dont know if its right 

 Create FUNCTION GetFileName
    (
     @fullpath nvarchar(260)
    ) 
    RETURNS nvarchar(260)
    AS
    BEGIN
    DECLARE @charIndexResult int
    SET @charIndexResult = CHARINDEX('\', REVERSE(@fullpath))

    IF @charIndexResult = 0
        RETURN NULL

I have tried doing that but I dont really know what i'm doing as I am stuck after that code

Upvotes: 0

Views: 628

Answers (1)

Gandalf
Gandalf

Reputation: 9855

You'll need to use non-standard SQL to create a recursive query, look into Common Table Expressions.

Upvotes: 1

Related Questions