• Shabbaranks - Thursday, April 27, 2017 7:30 AM

    Thanks for the input guys its really appreciated (all be it I am a little lost). My SQL knowledge is limited hence me being lost so please be patient with me 🙂

    How do you implement a function within an SQL query? Also as above when you say "a way of including test data" the data I am after is within a result of an initial query which returns the whole path. Can you query a query to then reduce that to only show the info within the 3rd and fourth "\" backslash?

    Thanks guys 🙂

    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