Select the document name from a column contaning full path and file name

Question

Select the document name from a column contaning full path and file name

Steve Kinnaman

SSC Eights!

Points: 839
More actions
November 4, 2011 at 8:09 am

#259545

I need to select the file name from a column in a database containing the full path and file name. What I want is to select all from the last \ (or the first \ from the right)
The column name:
dbo.Docs.Document_and_path
Contents of dbo.Docs.Document_and_path
F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc
F:\Offerter\Gammalt\2003 Frakt tillkommer\Tagna\Offert D-data_A4702_PJK.doc

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Steve Kinnaman (11/4/2011)
I need to select the file name from a column in a database containing the full path and file name. What I want is to select all from the last \ (or the first \ from the right)
The column name:
dbo.Docs.Document_and_path
Contents of dbo.Docs.Document_and_path
F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc
F:\Offerter\Gammalt\2003 Frakt tillkommer\Tagna\Offert D-data_A4702_PJK.doc

How's this?

IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment

END

CREATE TABLE #testEnvironment ([Document_and_path] VARCHAR(200))

INSERT INTO #testEnvironment

SELECT 'F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc'

UNION ALL SELECT 'F:\Offerter\Gammalt\2003 Frakt tillkommer\Tagna\Offert D-data_A4702_PJK.doc'

SELECT REVERSE(SUBSTRING(REVERSE([Document_and_path]),1,CHARINDEX('\',REVERSE([Document_and_path]),1)-1))

FROM #testEnvironment

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

TomThomson SSC Guru Points: 104773 More actions · Answer 2

One way to do it is

SELECT REVERSE(LEFT(REVERSE(Document_and_path), CHARINDEX(REVERSE(Document_and_path),'/')-1)

FROM dbo.Docs

edit: I see someone else posted the same way of doing it while i was typing.

Another way of doing it:

WITH X(rev) as (SELECT REVERSE(Document_and_path) rev FROM dbo.Docs)

SELECT REVERSE(LEFT(rev),CHARINDEX(rev,'/',1)-1)

but that probably is going to be slower.

Tom

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Another way: -

SELECT SUBSTRING([Document_and_path],length-(fromEnd-2),length)

FROM (SELECT CHARINDEX('\',REVERSE([Document_and_path])) AS fromEnd,

LEN([Document_and_path]) AS length, [Document_and_path]

FROM #testEnvironment) a

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 4

Both your suggestions worked! Than You!!

Could you help me with the next issue: I would like the full path in one column AS Path and the document name in another column. AS FileName

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Steve Kinnaman (11/4/2011)
Both your suggestions worked! Than You!!
Could you help me with the next issue: I would like the full path in one column AS Path and the document name in another column. AS FileName

Here's my two: -

--==Method 1

SELECT REVERSE(SUBSTRING(REVERSE([Document_and_path]),1,CHARINDEX('\',REVERSE([Document_and_path]),1)-1)) AS FileName,

SUBSTRING([Document_and_path],1,LEN([Document_and_path])-(CHARINDEX('\',REVERSE([Document_and_path]),1)-1)) AS Path

FROM #testEnvironment

--==Method 2

SELECT SUBSTRING([Document_and_path],length-(fromEnd-2),length) AS FileName,

SUBSTRING([Document_and_path],1,length-(fromEnd-1)) AS Path

FROM (SELECT CHARINDEX('\',REVERSE([Document_and_path])) AS fromEnd,

LEN([Document_and_path]) AS length, [Document_and_path]

FROM #testEnvironment) a

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 6

Steve Kinnaman

SSC Eights!

Points: 839

November 4, 2011 at 8:57 am

#1403299

Absolutly amazing! Thank you all so much

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

Cadavre (11/4/2011)
Steve Kinnaman (11/4/2011)
Both your suggestions worked! Than You!!
Could you help me with the next issue: I would like the full path in one column AS Path and the document name in another column. AS FileName
Here's my two: -
--==Method 1
SELECT REVERSE(SUBSTRING(REVERSE([Document_and_path]),1,CHARINDEX('\',REVERSE([Document_and_path]),1)-1)) AS FileName,
SUBSTRING([Document_and_path],1,LEN([Document_and_path])-(CHARINDEX('\',REVERSE([Document_and_path]),1)-1)) AS Path
FROM #testEnvironment
--==Method 2
SELECT SUBSTRING([Document_and_path],length-(fromEnd-2),length) AS FileName,
SUBSTRING([Document_and_path],1,length-(fromEnd-1)) AS Path
FROM (SELECT CHARINDEX('\',REVERSE([Document_and_path])) AS fromEnd,
LEN([Document_and_path]) AS length, [Document_and_path]
FROM #testEnvironment) a

Just a suggestion...You can simplify a lot by using CROSS APPLY instead of a "Derived Table" or CTE...

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Docs','U') IS NOT NULL DROP TABLE #Docs;

--===== Create the test table

CREATE TABLE #Docs ([Document_and_path] VARCHAR(200));

--===== Add the given test data to the test table

INSERT INTO #Docs

([Document_and_path])

SELECT 'F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc' UNION ALL

SELECT 'F:\Offerter\Gammalt\2003 Frakt tillkommer\Tagna\Offert D-data_A4702_PJK.doc'

;

--===== Split the path name from the file name and display both.

SELECT FilePath = SUBSTRING(Document_and_path, 1, d.FirstLength),

FileName = SUBSTRING(Document_and_path, d.FirstLength + 2, 8000) --See the second trick here?

FROM #Docs

CROSS APPLY (SELECT DATALENGTH([Document_and_path])-CHARINDEX('\',REVERSE([Document_and_path]))) d (FirstLength)

;

There's a secondary trick in the code above to shorten it quite a bit. Do you see it?

For those using 3 REVERSEs, remember that REVERSE is fairly expensive. It won't show up as being expensive on a handfull of rows but it can become comparatively time consuming on a million rows. Think you won't ever have a million rows of data for something like this? Heh... yeah... that's what I used to think a long time ago. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 8

I have now read the info at http://www.sqlservercentral.com/articles/Best+Practices/61537/

And saw by your exampel how to post code. I will use these tips for future posts. Thanks for the advice!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Thanks for the feedback, Steve.

Yeah... most of the "old hands" at this really like it a lot when someone takes the time to post readily consumable data. Saves a lot of time for them because of the number of posts they answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 10

Why doesn't this work? The CROSS APPLY suggestion is closest to what I need (I don't want the trailing \ in the path.) Testing your exempel works fine but when I try to adapt it to my table it doesn't work. What am I doing wrong? (The table and columns are different and I have the key field included)

use tempdb IF OBJECT_ID('tempdb..[Dokument$]','U')IS NOT NULL DROP TABLE [Dokument$]

CREATE TABLE [dbo].[Dokument$]([IDKEY] [nvarchar](255) NULL,[OFFERT] [nvarchar](255) NULL,)

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES('4E91FC14D3794F12AB981ED08F798EF7', 'F:\Offerter\2009\Skatteverket\Verksamhetsutveckling\Skatteverket_offert_A1601_Verksamhetsutveckling_med_processer.doc')

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES ('0ECD1FE160524506BB3DC4128FE4BA4D','F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc')

SELECT FilePath = SUBSTRING([OFFERT], 1, d.FirstLength)

, FileName = SUBSTRING([OFFERT], d.FirstLength + 2, 8000) --See the second trick here?

FROM Dokument$

CROSS APPLY (SELECT DATALENGTH([OFFERT])-CHARINDEX('\',REVERSE([OFFERT]))) d (FirstLength);

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 11

There is a big difference between nvarchar and varchar i now understand. Using varchar, it works pervectly. Thanks agan all who have helped me on this question:

This is the script that works:

use tempdb IF OBJECT_ID('tempdb..[Dokument$]','U')IS NOT NULL DROP TABLE [Dokument$]

CREATE TABLE [dbo].[Dokument$]([IDKEY] [varchar](255) NULL,[OFFERT] [varchar](255) NULL,)

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES('4E91FC14D3794F12AB981ED08F798EF7', 'F:\Offerter\2009\Skatteverket\Verksamhetsutveckling\Skatteverket_offert_A1601_Verksamhetsutveckling_med_processer.doc')

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES ('0ECD1FE160524506BB3DC4128FE4BA4D','F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc')

SELECT FilePath = SUBSTRING([OFFERT], 1, d.FirstLength)

, FileName = SUBSTRING([OFFERT], d.FirstLength + 2, 8000) --See the second trick here?

FROM Dokument$

CROSS APPLY (SELECT DATALENGTH([OFFERT])-CHARINDEX('\',REVERSE([OFFERT]))) d (FirstLength);

TomThomson SSC Guru Points: 104773 More actions · Answer 12

Here's the version that works for nvarchar.

use tempdb

IF OBJECT_ID('tempdb..[Dokument$]','U')IS NOT NULL DROP TABLE [Dokument$]

CREATE TABLE [dbo].[Dokument$](

[IDKEY] [nvarchar](255) NULL,[OFFERT] [nvarchar](255) NULL,)

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT])

VALUES(N'4E91FC14D3794F12AB981ED08F798EF7', N'F:\Offerter\2009\Skatteverket

\Verksamhetsutveckling\Skatteverket_offert_A1601_Verksamhetsutveckling_med_processer.doc')

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES

(N'0ECD1FE160524506BB3DC4128FE4BA4D',N'F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc')

SELECT FilePath = SUBSTRING([OFFERT], 1, d.FirstLength)

, FileName = SUBSTRING([OFFERT], d.FirstLength + 2, 8000) --See the second trick here?

FROM Dokument$

CROSS APPLY (SELECT DATALENGTH([OFFERT])/2-CHARINDEX('\',REVERSE([OFFERT]))) d (FirstLength);

Two changes: nvarchar string literals introduced with N', and the datalegth (in bytes) divided by 2 to get the number of 2-byte characters.

Tom

Steve Kinnaman SSC Eights! Points: 839 More actions · Answer 13

Yes That work great. A friend suggested to replace DATALENGTH with LEN, and that worked as well.

use tempdb IF OBJECT_ID('tempdb..[Dokument$]','U')IS NOT NULL DROP TABLE [Dokument$]

CREATE TABLE [dbo].[Dokument$]([IDKEY] [nvarchar](255) NULL,[OFFERT] [nvarchar](255) NULL,)

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES('4E91FC14D3794F12AB981ED08F798EF7', 'F:\Offerter\2009\Skatteverket\Verksamhetsutveckling\Skatteverket_offert_A1601_Verksamhetsutveckling_med_processer.doc')

INSERT INTO [dbo].[Dokument$] ([IDKEY],[OFFERT]) VALUES ('0ECD1FE160524506BB3DC4128FE4BA4D','F:\Offerter\2005\Posten\NYTT RAMAVTAL 2005-04-20\ramavtal utbildning personlig effektivitet Astrakan.doc')

SELECT FilePath = SUBSTRING([OFFERT], 1, d.FirstLength)

, FileName = SUBSTRING([OFFERT], d.FirstLength + 2, 8000) --See the second trick here?

FROM Dokument$

CROSS APPLY (SELECT LEN([OFFERT])-CHARINDEX('\',REVERSE([OFFERT]))) d (FirstLength);

--Thanks for your suggestions!

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Steve Kinnaman (11/7/2011)
Yes That work great. A friend suggested to replace DATALENGTH with LEN, and that worked as well.

Using LEN instead of DATALENGTH will break if ever a filename has trailing spaces; usually filenames don't but sometimes spaces do get entered accidentally into tables at the end of a column - they are hard to see, so they sometimes stay there.

Tom