SQLTeam.com | Weblogs | Forums

SQL Query Substring Reverse Extract last file name from file path

sql2008

#1

Hi

I am trying to extract the last file name from a file path so in this instance ABFT.

/Standards Development /DATABASE SERVICES/Trade associations/ABFT/

I have tried using reverse and substring without success, any help would be really appreciated as I am really struggling with this one.

Thank you

Lyn3009


#2

The following is deliberately a little long-winded to make it easier to understand, so you can extend it or adapt it as you need.

DECLARE @x VARCHAR(256) = '/Standards Development /DATABASE SERVICES/Trade associations/ABFT/'

SELECT 
	REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1))
FROM
	( VALUES (REVERSE(@x)) ) A(a)
	CROSS APPLY( VALUES (STUFF(a,1,CHARINDEX('/',a+'/'),'')) )B(b);

#3

Hi James
Thank you. How do I include it in this query. Sorry I don't know much about using declare. the field is ADS_EVENT.Object_FolderPath

SELECT
ADS_EVENT.Top_Folder_Name,
ADS_EVENT.Object_Name,
ADS_EVENT.Object_Folder_Path,
ADS_EVENT.User_Name,
CAST( ADS_EVENT.Start_Time AS CHAR ),
ADS_OBJECT_TYPE_STR.Object_Type_Name,
Convert(float,ADS_EVENT.Duration_ms) / 1000,
CASE ADS_STATUS_STR.Status_ID WHEN 0 THEN 'successful' ELSE 'failed' END
FROM
ADS_EVENT_TYPE_STR INNER JOIN ADS_EVENT_TYPE ON (ADS_EVENT_TYPE.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language = 'en')
INNER JOIN ADS_EVENT ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE.Event_Type_ID)
INNER JOIN ADS_OBJECT_TYPE_STR ON (ADS_EVENT.Object_Type_ID=ADS_OBJECT_TYPE_STR.Object_Type_ID AND ADS_OBJECT_TYPE_STR.Language = 'en')
INNER JOIN ADS_STATUS_STR ON (ADS_EVENT.Event_Type_ID=ADS_STATUS_STR.Event_Type_ID and ADS_EVENT.Status_ID=ADS_STATUS_STR.Status_ID AND ADS_STATUS_STR.Language = 'en')

WHERE
(
ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND
datepart(yyyy, ADS_EVENT.Start_Time) >= 2016
AND
ADS_EVENT.Top_Folder_Name NOT IN ( '','Auditing','Feature Samples','Functions & Templates','IT SERVICES','IT SERVICES - Copy (1)','IT SERVICES - OLD','IT SERVICES [1]','itelliTest','LCM','Report Conversion Tool','Report Samples','Report Samples - Copy (1)','Web Intelligence Samples' )
)

Thank You


#4
SELECT  ADS_EVENT.Top_Folder_Name ,
        ADS_EVENT.Object_Name ,
        ADS_EVENT.Object_Folder_Path ,
        ADS_EVENT.User_Name ,
        CAST(ADS_EVENT.Start_Time AS CHAR) ,
        ADS_OBJECT_TYPE_STR.Object_Type_Name ,
        CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 ,
        CASE ADS_STATUS_STR.Status_ID
          WHEN 0 THEN 'successful'
          ELSE 'failed'
        END,
        --- ADDED THIS COLUMN
        REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS FileName
FROM    ADS_EVENT_TYPE_STR
        INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
                                       AND ADS_EVENT_TYPE_STR.Language = 'en'
                                     )
        INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
        INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
                                            AND ADS_OBJECT_TYPE_STR.Language = 'en'
                                          )
        INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
                                       AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
                                       AND ADS_STATUS_STR.Language = 'en'
                                     )
		-- ADDED THIS CROSS APPLY                                    
		CROSS APPLY( VALUES (STUFF(ADS_EVENT.Object_FolderPath,1,CHARINDEX('/',ADS_EVENT.Object_FolderPath+'/'),'')) ) AS B(b)                                    
WHERE   ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
          AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
          AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
                                                 'Feature Samples',
                                                 'Functions & Templates',
                                                 'IT SERVICES',
                                                 'IT SERVICES - Copy (1)',
                                                 'IT SERVICES - OLD',
                                                 'IT SERVICES [1]',
                                                 'itelliTest', 'LCM',
                                                 'Report Conversion Tool',
                                                 'Report Samples',
                                                 'Report Samples - Copy (1)',
                                                 'Web Intelligence Samples' )
        );

#5

Hi James

Thanks ever so much for your help. Unfortunately when I enter this query into the BI Launch pad web intelligence I get the following error message

The query SQL has 9 columns instead of 8 columns.(WIS 10810)
Cause The SQL generated by the query has an invalid number of columns.

Lyn


#6

Another method would be

DECLARE @x VARCHAR(256) = '/Standards Development /DATABASE SERVICES/Trade associations/ABFT/'

select right(x,charindex('/',reverse(x))-1)
from
(
select substring(@x,1,len(@x)-1) as x
) as t


#7

In the query I posted I ADDED a column - see the line below the comment "--- ADDED THIS COLUMN". Perhaps that is not what you want? Did you want to replace one of the existing columns with that?


#8

Hi James
if we can replace either the Object_type_name or the object_folder_path that would be great.
Thank you again
Lyn


#9

You can remove the columns or move columns around, as in the example below where I replaced the Object_Type_Name with the new value

SELECT  ADS_EVENT.Top_Folder_Name ,
        ADS_EVENT.Object_Name ,
        ADS_EVENT.Object_Folder_Path ,
        ADS_EVENT.User_Name ,
        CAST(ADS_EVENT.Start_Time AS CHAR) ,
        --ADS_OBJECT_TYPE_STR.Object_Type_Name ,
        REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS Object_Type_Name
        CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 ,
        CASE ADS_STATUS_STR.Status_ID
          WHEN 0 THEN 'successful'
          ELSE 'failed'
        END
        
FROM    ADS_EVENT_TYPE_STR
...rest of your code here.

#10

Hi James
Thank you for all your help.

I have now got the code

SELECT ADS_EVENT.Top_Folder_Name ,
ADS_EVENT.Object_Name ,
ADS_EVENT.Object_Folder_Path ,
ADS_EVENT.User_Name ,
CAST(ADS_EVENT.Start_Time AS CHAR) ,
--ADS_OBJECT_TYPE_STR.Object_Type_Name ,
REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS Object_Type_Name
CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 ,
CASE ADS_STATUS_STR.Status_ID
WHEN 0 THEN 'successful'
ELSE 'failed'
END,
--- ADDED THIS COLUMN
---REVERSE(LEFT(b,CHARINDEX('/',b+'/')-1)) AS FileName
FROM ADS_EVENT_TYPE_STR
INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
AND ADS_EVENT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
AND ADS_OBJECT_TYPE_STR.Language = 'en'
)
INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
AND ADS_STATUS_STR.Language = 'en'
)
-- ADDED THIS CROSS APPLY
CROSS APPLY( VALUES (STUFF(ADS_EVENT.Object_FolderPath,1,CHARINDEX('/',ADS_EVENT.Object_FolderPath+'/'),'')) ) AS B(b)
WHERE ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
'Feature Samples',
'Functions & Templates',
'IT SERVICES',
'IT SERVICES - Copy (1)',
'IT SERVICES - OLD',
'IT SERVICES [1]',
'itelliTest', 'LCM',
'Report Conversion Tool',
'Report Samples',
'Report Samples - Copy (1)',
'Web Intelligence Samples' )

However it is still showing the folder path in exactly the same order with the / at both the beginning and the end, I am really really confused now.

Lyn


#11

Hi Madhivanan
Thanks for the response, as I said to James I am really a bit thick when it comes to using declare.

How would I use it in the original query, Web intelligence does not allow you to add in an extra column in the SQL query.

Hi James
Thank you. How do I include it in this query. Sorry I don't know much about using declare. the field is ADS_EVENT.Object_FolderPath

SELECT
ADS_EVENT.Top_Folder_Name,
ADS_EVENT.Object_Name,
ADS_EVENT.Object_Folder_Path,
ADS_EVENT.User_Name,
CAST( ADS_EVENT.Start_Time AS CHAR ),
ADS_OBJECT_TYPE_STR.Object_Type_Name,
Convert(float,ADS_EVENT.Duration_ms) / 1000,
CASE ADS_STATUS_STR.Status_ID WHEN 0 THEN 'successful' ELSE 'failed' END
FROM
ADS_EVENT_TYPE_STR INNER JOIN ADS_EVENT_TYPE ON (ADS_EVENT_TYPE.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language = 'en')
INNER JOIN ADS_EVENT ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE.Event_Type_ID)
INNER JOIN ADS_OBJECT_TYPE_STR ON (ADS_EVENT.Object_Type_ID=ADS_OBJECT_TYPE_STR.Object_Type_ID AND ADS_OBJECT_TYPE_STR.Language = 'en')
INNER JOIN ADS_STATUS_STR ON (ADS_EVENT.Event_Type_ID=ADS_STATUS_STR.Event_Type_ID and ADS_EVENT.Status_ID=ADS_STATUS_STR.Status_ID AND ADS_STATUS_STR.Language = 'en')

WHERE
(
ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run'
AND
datepart(yyyy, ADS_EVENT.Start_Time) >= 2016
AND
ADS_EVENT.Top_Folder_Name NOT IN ( '','Auditing','Feature Samples','Functions & Templates','IT SERVICES','IT SERVICES - Copy (1)','IT SERVICES - OLD','IT SERVICES [1]','itelliTest','LCM','Report Conversion Tool','Report Samples','Report Samples - Copy (1)','Web Intelligence Samples' )
)


#12

Since I don't have access to your system, I can only be of limited help here. You have to look at the query and correlate that with what your front-end client wants to get.

A query has several sections/logical pieces. In the code below, I have intended the text so you can distinguish different sections .

Look at the first section which is the one that starts with SELECT. It returns 8 columns which I have numbered. Anything after a dash-dash ( -- ) is a comment, so you can ignore all of that. Are those 8 columns the ones your client front end expects? For example, there is a column called Object_Folder_Path. That is probably what you are seeing with the full path name. The column that I added is #6 named Object_Type_Name.

You don't have to try to become an expert, but read up a little bit on SELECT statements, for example here.

-----------------------------------------------------------------------------
SELECT  ADS_EVENT.Top_Folder_Name ,  -- 1
        ADS_EVENT.[Object_Name] , -- 2
        ADS_EVENT.Object_Folder_Path , -- 3
        ADS_EVENT.User_Name , -- 4
        CAST(ADS_EVENT.Start_Time AS CHAR) , -- 5
        REVERSE(LEFT(b, CHARINDEX('/', b + '/') - 1)) AS Object_Type_Name , -- 6
        CONVERT(FLOAT, ADS_EVENT.Duration_ms) / 1000 , -- 7
        CASE ADS_STATUS_STR.Status_ID
          WHEN 0 THEN 'successful'
          ELSE 'failed'
        END -- 8
        
        
-----------------------------------------------------------------------------


FROM    ADS_EVENT_TYPE_STR
        INNER JOIN ADS_EVENT_TYPE ON ( ADS_EVENT_TYPE.Event_Type_ID = ADS_EVENT_TYPE_STR.Event_Type_ID
                                       AND ADS_EVENT_TYPE_STR.Language = 'en'
                                     )
        INNER JOIN ADS_EVENT ON ( ADS_EVENT.Event_Type_ID = ADS_EVENT_TYPE.Event_Type_ID )
        INNER JOIN ADS_OBJECT_TYPE_STR ON ( ADS_EVENT.Object_Type_ID = ADS_OBJECT_TYPE_STR.Object_Type_ID
                                            AND ADS_OBJECT_TYPE_STR.Language = 'en'
                                          )
        INNER JOIN ADS_STATUS_STR ON ( ADS_EVENT.Event_Type_ID = ADS_STATUS_STR.Event_Type_ID
                                       AND ADS_EVENT.Status_ID = ADS_STATUS_STR.Status_ID
                                       AND ADS_STATUS_STR.Language = 'en'
                                     )
-- ADDED THIS CROSS APPLY 
        CROSS APPLY ( VALUES
        ( STUFF(ADS_EVENT.Object_FolderPath, 1,
                CHARINDEX('/', ADS_EVENT.Object_FolderPath + '/'), '')) ) AS B ( b )
                
                
-----------------------------------------------------------------------------


WHERE   ( ADS_EVENT_TYPE_STR.Event_Type_Name = 'Run' )
        AND DATEPART(yyyy, ADS_EVENT.Start_Time) >= 2016
        AND ADS_EVENT.Top_Folder_Name NOT IN ( '', 'Auditing',
                                               'Feature Samples',
                                               'Functions & Templates',
                                               'IT SERVICES',
                                               'IT SERVICES - Copy (1)',
                                               'IT SERVICES - OLD',
                                               'IT SERVICES [1]', 'itelliTest',
                                               'LCM', 'Report Conversion Tool',
                                               'Report Samples',
                                               'Report Samples - Copy (1)',
                                               'Web Intelligence Samples' );
-----------------------------------------------------------------------------