CODEWITHSUNDEEP
sqlsql-servert-sql
Kalu
Kalu

Reputation: 3

A SQL Query to select certain strings in a folder path

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!

Upvotes: 0

Views: 1529

Answers (3)

Kalu
Kalu

Reputation: 3

Thank you @Dale K for your response and solutions provided. I was able to replicate the same for my query to obtain the result. Below is how I modified the query in my environment to fetch only the new string column1 after applying the string manipulations based on your solution:

SELECT SUBSTRING(Y.column1, LEN(Y.column1) - PATINDEX('%%', REVERSE(Y.column1)) +2, LEN(Y.column1)) FROM (SELECT column1 FROM CTE1) AS X ([column2]) CROSS APPLY (SELECT SUBSTRING(X.column2, 1, PATINDEX('%.dtsx%', X.column2)-1) FROM CTE1) AS Y ([column1])

I am actually query a CTE table (CTE1) to get my desired result. The issue is that, I have other columns in the CTE1 that I need to include in the final select query results, which of course should include the string manipulated results from Column1. Currently, I get errors when I try to include other columns in my final result from the CTE1 along with the resultset from the query above. Example of final query:

Select Jobname,job_step,job_date,job_duration,Column1 (this will be the resultset from the string manipulation)FROM CTE1;

So, what I'm currently doing that is not working is as follows:

SELECT C1.Jobname,C1.job_step,C1.job_date,C1.job_duration,Column1 =(SELECT SUBSTRING(Y.column1, LEN(Y.column1) - PATINDEX('%\%', REVERSE(Y.column1)) +2, LEN(Y.column1)) FROM (SELECT column1 FROM CTE1) AS X ([column2]) CROSS APPLY (SELECT SUBSTRING(X.column2, 1, PATINDEX('%.dtsx%', X.column2)-1) FROM CTE1) AS Y ([column1])) FROM CTE1 C1

Please, how can I obtain the final results with all the above columns present in the resultsets? Thank you.

Upvotes: 0

Nathan Fisher
Nathan Fisher

Reputation: 7941

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'

Upvotes: 0

Dale K
Dale K

Reputation: 27202

  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

Upvotes: 1

Related Questions