Removing folder path and extension from SQL column

Question

I have done some research, but I have been unable to do/find how to put the two together.

I am trying to get the filename from a field known as doc_filename, minus any document path and extension, for example "docs\00010\filename.doc" should be just be "filename".

This has worked to remove the path name, however the extension remains. How do I remove this?

Thanks

SELECT (case when doc_filename like '%\%' then 
 LTRIM(
  RTRIM(
   REVERSE(
    SUBSTRING(
     REVERSE(doc_filename),0, CHARINDEX('\', REVERSE(doc_filename),0)
    )
   )
  )
 )
 else ''
 end)

FROM documents

Possible duplicate of [Parse file name and path from full path](http://stackoverflow.com/questions/19505875/parse-file-name-and-path-from-full-path) — Backs, Feb 02 '16 at 05:51

TT. · Accepted Answer · 2016-02-02T08:21:22.073

By way of an example:

DECLARE @fps TABLE(fp VARCHAR(512));
INSERT INTO @fps(fp)VALUES
    ('\filename.001.doc'),
    ('docs\00010\filename.doc'),
    ('docs\00010\filename'),
    ('filename.doc'),
    ('filename'),
    ('\filename'),
    ('\filename.001.doc'); 
    -- ('.\filename'); --> this one will fail

SELECT 
    CASE 
        WHEN CHARINDEX('.',REVERSE(fp))=0 AND CHARINDEX('\',REVERSE(fp))=0 THEN fp
        WHEN CHARINDEX('.',REVERSE(fp))=0 THEN RIGHT(fp,CHARINDEX('\',REVERSE(fp))-1)
        WHEN CHARINDEX('\',REVERSE(fp))=0 THEN LEFT(fp,LEN(fp)-CHARINDEX('.',REVERSE(fp)))
        ELSE SUBSTRING(fp,LEN(fp)-CHARINDEX('\',REVERSE(fp))+2,CHARINDEX('\',REVERSE(fp))-CHARINDEX('.',REVERSE(fp))-1)
    END AS [file_name]
FROM
    @fps;

Result:

+--------------+
|  file_name   |
+--------------+
| filename.001 |
| filename     |
| filename     |
| filename     |
| filename     |
| filename     |
| filename.001 |
+--------------+

Removing folder path and extension from SQL column

1 Answers1