Shabbaranks - Thursday, April 27, 2017 7:30 AM
It would go something like this...
1) Create a few rows of test data... (temp table called #Paths)
IF OBJECT_ID('tempdb..#Paths', 'U') IS NOT NULL
DROP TABLE #Paths;
GO
CREATE TABLE #Paths (
FilePath VARCHAR(1000) NOT NULL
);
INSERT #Paths (FilePath) VALUES
('\ENQ123\Testing Data\Testing More Data\Test'),
('\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing'),
('\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456');
GO
2) Create a new inline table valued function (dbo.tfn_ThirdFilePathNode)
CREATE FUNCTION dbo.tfn_ThirdFilePathNode
/* =========================================================
04/27/2017 Created... Returns the 3rd node in a file path
========================================================= */
(
@FilePath VARCHAR(1000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT
SubPathName = SUBSTRING(@FilePath, s3.S3_Pos, s4.S4_Pos - s3.S3_Pos)
FROM
( VALUES (CHARINDEX('\', @FilePath, 2)) ) s2 (S2_Pos)
CROSS APPLY ( VALUES (CHARINDEX('\', @FilePath, s2.S2_Pos + 1) + 1) ) s3 (S3_Pos)
CROSS APPLY ( VALUES (CHARINDEX('\', @FilePath, s3.S3_Pos + 1)) ) s4 (S4_Pos);
GO
3) Use the function against the test data created in step 1...
SELECT
p.FilePath,
tfpn.SubPathName
FROM
#Paths p
CROSS APPLY dbo.tfn_ThirdFilePathNode(p.FilePath) tfpn;
And finally, some results...
FilePath SubPathName
--------------------------------------------------------------------- ----------------------------
\ENQ123\Testing Data\Testing More Data\Test Testing More Data
\ENQ345\Testing Data2\Testing More Data1234\Test 123\Testing Testing More Data1234
\ENQ456\Testing Data3\Testing More Data45678\Test ABCD\Testing 1234456 Testing More Data45678