I have a table with a column that contains the path to SSIS packages located in a drive. The entire folder path is populated in the column. I need a SQL query to get a section of the string within the folder path.

An example of record in the column_1.

/FILE "\"G:\Enterprise_Data\Packages\SSIS_Packages_Source_to_Target_Data_Snowflake.dtsx\""/CHECKPOINTING OFF /REPORTING E

All I am interested in extracting is the "SSIS_Packages_Source_to_Target_Data_Snowflake". Everything I have tried so far throws errors. The latest code I tried is:

SELECT SUBSTRING(Column_1, LEFT(CHARINDEX('dtsx', Column_1)), LEN(Column_1) - CHARINDEX('dtsx', Column_1)).

I would really appreciate some help with this. Thanks!

CodePudding user response：

1. Given you know the extension and its unlikely to appear elsewhere in the string, find it, and truncate to it. Do that in a CROSS APPLY so we can use the value multiple times.
2. Then find the nearest slash (using REVERSE) and use SUBSTRING from there to the end.

SELECT
    SUBSTRING(Y.[VALUE], LEN(Y.[VALUE]) - PATINDEX('%\%', REVERSE(Y.[VALUE]))   2, LEN(Y.[VALUE]))
FROM (
    VALUES ('/FILE "\"G:\Enterprise_Data\Packages\SSIS_Packages_Source_to_Target_Data_Snowflake.dtsx\""/CHECKPOINTING OFF /REPORTING E')
) AS X ([Value])
CROSS APPLY (
    VALUES (SUBSTRING(X.[Value], 1, PATINDEX('%.dtsx%', X.[Value])-1))
) AS Y ([Value]);

Returns:

SSIS_Packages_Source_to_Target_Data_Snowflake

CodePudding user response：

Another possible way is this. not sure on the performance of it though

SELECT vt.[value]
FROM (
    VALUES ('/FILE "\"G:\Enterprise_Data\Packages\SSIS_Packages_Source_to_Target_Data_Snowflake.dtsx\""/CHECKPOINTING OFF /REPORTING E')
) AS X ([Value])
OUTER APPLY (
    SELECT * FROM STRING_SPLIT(x.Value,'\')
) vt
WHERE vt.[value] LIKE '%.dtsx'