Trim or substring?

1

I'm trying to find a way to return 151BKT27 A.PDF from Q:\BKT_PDF\GROUND\ELECTRICAL\151BKT27 A.PDF

I'm assuming its a LTRIM or SUBSTRING function but am trying to figure out the syntax.

Any assistance is appreciated.

Thank you.

2

I need to also note, in many cases the file path is different. I basically need the filname:

Q:\GRD_PDF\GROUND\151BKT9356 A.PDF > 151BKT9356 A.PDF
I:\BKT_PDF\GROUND\ELECTRICAL\151BKT27 A.PDF > 151BKT27 A.PDF
C:\BKT_PDF\ELECTRICAL\151BDFRKT88 A.PDF > 151BDFRKT88 A.PDF

3 
-- *** Consumable Test Data ***
-- You should provide this
CREATE TABLE #t
(
    FilePath nvarchar(512) NOT NULL
);
INSERT INTO #t
VALUES ('Q:\GRD_PDF\GROUND\151BKT9356 A.PDF')
    ,('I:\BKT_PDF\GROUND\ELECTRICAL\151BKT27 A.PDF')
    ,('C:\BKT_PDF\ELECTRICAL\151BDFRKT88 A.PDF');
-- *** End Consumable Test Data ***

SELECT T.FilePath
    ,REVERSE(LEFT(A.RFilePath, CHARINDEX('\', A.RFilePath) - 1)) AS FName
FROM #t T
    CROSS APPLY
    (VALUES (REVERSE(T.FilePath))) A (RFilePath);
4

Using the sample data from above:

SELECT FilePath, 
    RIGHT(FilePath, CHARINDEX('\', REVERSE(FilePath)) - 1) AS File_Name
FROM #t
1 Like
5

If you might have a file in the root folder, so that there is no \ in the path and file name, you would need to do something like this:

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t;
CREATE TABLE #t
(
    FilePath nvarchar(512) NOT NULL
);
INSERT INTO #t
VALUES ('Q:\GRD_PDF\GROUND\151BKT9356 A.PDF')
    ,('I:\BKT_PDF\GROUND\ELECTRICAL\151BKT27 A.PDF')
    ,('C:\BKT_PDF\ELECTRICAL\151BDFRKT88 A.PDF')
    ,('C:A.PDF') /****No \, root-level file****/
    ,('N:') /****No filename at all****/
    
SELECT FilePath, 
    RIGHT(FilePath, ISNULL( NULLIF(CHARINDEX('\', REVERSE(FilePath)), 0), 
        NULLIF(CHARINDEX(':', REVERSE(FilePath)), 0) ) - 1) AS File_Name
FROM #t
1 Like
6

awesome. thanks so much for your help!

7

Since you didn't know that there is no "LTRIM" in SQL SERVER nor that "LTRIM" in most languages only strips out leading spaces, do you understand how the provided solution actually works?

8

i can reverse engineer it by looking at it, but it's a very basic understanding

9

K. Just wanted to make sure that you weren't left hanging if you have to explain it.

10

Actually - there is LTRIM and RTRIM in SQL Server. There is not a TRIM function though.

1 Like
11

Heh... finally. Someone passed "the test". I wanted to find out if the OP knew in a backhanded sort of way. :wink: