SQL Tips by Namwar Rizvi

June 22, 2007

Extracting FileName through TSQL

Filed under: Query,tips,TSQL,Utility Functions — namwar @ 11:27 PM

Today I am sharing with you a utility UDF function to extract the file name from the given file path. This TSQL function also demonstrates the use of finding last occurance of a given character by using REVERSE function. Following is the TSQL code:

Create function dbo.udf_GetFileName(@m_FullFilePath varchar(255))
Returns varchar(50)

as

Begin

Return
Reverse(Left(Reverse(@m_FullFilePath),Charindex(‘\’,

Reverse(@m_FullFilePath))-1))

End

Now lets test this function:

Select dbo.udf_GetFileName(‘C:\Program Files\Adobe\Acrobat5.0\Help\ENU\ACROBAT.PDF’)

Result:
ACROBAT.PDF

Advertisements

2 Comments »

  1. came across from another site, I’d like to share my solution here too:

    declare @a varchar(8000)
    set @a = ‘C:\Program Files\Adobe\Acrobat5.0\Help\ENU\ACROBAT.PDF’

    select right(@a, charindex(‘\’, reverse(@a)) – 1)

    Comment by james gu — February 2, 2009 @ 3:25 AM | Reply

  2. Thank you for your trick using the reverse function! I liked it so much I wrote about it (and gave you credit for originating the idea :>).

    Thanks.

    Comment by PHenry — March 24, 2009 @ 2:39 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: