To write a function, to get the next folder level in the file path

Hi all,
         I am new to sql server, I need to do a task where a function should be created, and in that, a string of a file path will be given as an input parameter and it should give next folder level in the path as output.

For example, "Users\appu\Documents\Visual Studio 2008\Projects" is the file path, here if I give Users as input parameter it should give the next folder level, or if I give Documents as input parameter, it should give next folder level after that which is '\Visual Studio 2008'.
In this way I need to write a function, can you guys help me in this please.

Thanks,
Ranjit
AparanjithAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
DECLARE @Directory VARCHAR(MAX),
	@Folder VARCHAR(128)

SELECT @Directory = 'Users\appu\Documents\Visual Studio 2008\Projects',
	@Folder = 'Documents'
	
DECLARE @Start INT,
	@End INT,
	@NextFolder VARCHAR(128)
	
SET @Start = PATINDEX('%\' + @Folder + '\%', @Directory)

IF @Start = 0
BEGIN
	PRINT 'NOT FOUND'
END
ELSE
BEGIN
	SET @Start = @Start + LEN(@Folder) + 2
	SET @End = CHARINDEX('\', @Directory, @Start)
	SET @NextFolder = SUBSTRING(@Directory, @Start, @End - @Start)
        PRINT @NextFolder
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AparanjithAuthor Commented:
Hi BriCrowe,
                        By executing the query I am getting the result, but I want it in function, I tried that but I am facing some issues. Can you please wrap this code into function for me, I will be very thankful to you.

Thanks,
Ranjit
0
Brian CroweDatabase AdministratorCommented:
IF OBJECT_ID(N'dbo.GetNextFolder', N'FN') IS NOT NULL
    DROP FUNCTION dbo.GetNextFolder;
GO

CREATE FUNCTION dbo.GetNextFolder (@Directory VARCHAR(8000), @Folder VARCHAR(128))
RETURNS VARCHAR(128)
AS
	BEGIN

	DECLARE @Start INT,
		@End INT,
		@Length INT,
		@NextFolder VARCHAR(128)
	
	SELECT @Start = PATINDEX('%\' + @Folder + '\%', @Directory),
		@Length = LEN(@Folder) + 2
	IF @Start = 0	--Check for root folder
	BEGIN
		SELECT @Start = PATINDEX(@Folder + '\%', @Directory),
			@Length = LEN(@Folder) + 1
	END

	IF @Start = 0
	BEGIN
		RETURN ''
	END

	SET @Start = @Start + @Length
	SET @End = CHARINDEX('\', @Directory, @Start)
	IF @End = 0	--Last Folder
	BEGIN
		SET @End = LEN(@Directory) + 1
	END
	RETURN SUBSTRING(@Directory, @Start, @End - @Start)
END

Open in new window


This would require some additional logic if you wanted to be able to search with wildcards i.e.

SELECT dbo.GetNextFolder('Users\appu\Documents\Visual Studio 2008\Projects', '%Studio%')

In that case I would probably change the logic to tokenize the folders into a table and then  just use LIKE against that table.
0
AparanjithAuthor Commented:
Hey BriCrowe,
                           Where can we cange the logic if we want rest of the path from the input value what we give say, if we give Documents as input parameter. I need the rest of the path as
\Visual Studio 2008\Projects, how can we do that?
0
Brian CroweDatabase AdministratorCommented:
replace the third parameter in the SUBSTRING function...

RETURN SUBSTRING(@Directory, @Start, LEN(@Directory))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.