May 5, 2013 at 10:25 pm
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
May 6, 2013 at 1:20 am
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.
May 6, 2013 at 5:30 am
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 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL
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
May 6, 2013 at 5:33 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply