Extract string between two special characters upto 4 sublevels

Question

Post reply

Extract string between two special characters upto 4 sublevels

ksrikanth77

SSCrazy

Points: 2232
More actions
May 5, 2013 at 10:25 pm

#293431

Hi Everyone,
Can anyone of you please help me to get the T-SQL to extract a string between two special characters upto three subfolder levels.
Example: /IT/Management,
/PRODUCTION/DESIGN
/Service/Billing/Cash/
/Service/Billing/Cash/Full
My Result should be in the Below format
MainFolderName SubFolderLevel1 SubFolderLevel2 SubFolderLevel3
IT MANAGEMENT NULL NULL
PRODUCTION DESIGN NULL NULL
Service Billing Cash NULL
Service Billing Cash FULL
Thanks in advance
Srikanth Reddy Kundur

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Pankaj067 Old Hand Points: 313 More actions · Answer 1

Hello,

You can use the extended stored procedure xp_cmdshell of SQL Server.

In order to use this first of all you have to reconfigure the run_value of xp_cmdshell by using sp_configure stored procedure.

In case you find it difficult to understand, feel free to ask.

Thanks.

Lowell SSC Guru Points: 323504 More actions · Answer 2

here's one solution using a scalar function. I don't wnat to hog the thread, so I'll leave it to others to post a nice ITVF function featuring cross apply, which is a little harder to understand, but an order of magnitude faster on large datasets:

With MySampleData(val)

As

(

SELECT '/IT/Management' UNION ALL

SELECT '/PRODUCTION/DESIGN' UNION ALL

SELECT '/Service/Billing/Cash/' UNION ALL

SELECT '/Service/Billing/Cash/Full'

)

SELECT dbo.fn_parsename(val,'/',1),

dbo.fn_parsename(val,'/',2),

dbo.fn_parsename(val,'/',3),

dbo.fn_parsename(val,'/',4),

dbo.fn_parsename(val,'/',5),*

FROM MySampleData

GO

CREATE FUNCTION dbo.fn_parsename

(

@pString VARCHAR(7999),

@pDelimiter CHAR(1),

@Occurrance int

)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @Results VARCHAR(8000)

--===== "Inline" CTE Driven "Tally Table” produces values up to

-- 10,000... enough to cover VARCHAR(8000)

;WITH

E1(N) AS ( --=== Create Ten 1's

SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 --10

),

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100

E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000

cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT N)) FROM E4) ,

--===== Do the split

InterResults

AS

(

SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,

SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item

FROM cteTally

WHERE N < LEN(@pString) + 2

AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter

)

SELECT @Results = Item FROM InterResults WHERE ItemNumber = @Occurrance

return @Results

END --FUNCTION

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

Pankaj067 (5/6/2013)
Hello,
You can use the extended stored procedure xp_cmdshell of SQL Server.
In order to use this first of all you have to reconfigure the run_value of xp_cmdshell by using sp_configure stored procedure.
In case you find it difficult to understand, feel free to ask.
Thanks.

This actually doesn't have anything to do with folders at the OS level so no xp_CmdShell required. The OP just wants to parse data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)