How to get file name? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get file name?

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-17 : 13:54:14

How do i get file name and extension only?

declare @tbl1 table
(ID INT,
FilePath varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'c:\abc\aa.txt'

INSERT INTO @tbl1
SELECT 2, 'f:\mnop\tt.xml'

INSERT INTO @tbl1
SELECT 3, '\\mycomp\me\mm.pp.nn.zz'

INSERT INTO @tbl1
SELECT 4, '\\yourcomp\doc\new folder\ff33.333.33.xml'

select * from @tbl1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-17 : 14:12:26

CAN U TRY THIS...

SELECT REVERSE(LEFT(REVERSE(FilePath),CHARINDEX('\',REVERSE(FilePath))-1))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-17 : 14:20:19

this is basically the same but takes into account a value with only the name (no path)


declare @tbl1 table
(ID INT,
FilePath varchar(255)
)

INSERT INTO @tbl1
SELECT 1, 'c:\abc\aa.txt'

INSERT INTO @tbl1
SELECT 2, 'f:\mnop\tt.xml'

INSERT INTO @tbl1
SELECT 3, '\\mycomp\me\mm.pp.nn.zz'

INSERT INTO @tbl1
SELECT 4, '\\yourcomp\doc\new folder\ff33.333.33.xml'

INSERT INTO @tbl1 SELECT 5, 'ff33.333.33.xml'

select filePath
       ,FileName = right(filePath, isNull(nullif(charindex('\', reverse(filepath)),0)-1,len(filepath))) 
from   @tbl1

output:
filePath                                          FileName
---------------------------------------------------------------------
c:\abc\aa.txt                                     aa.txt
f:\mnop\tt.xml                                    tt.xml
\\mycomp\me\mm.pp.nn.zz                           mm.pp.nn.zz
\\yourcomp\doc\new folder\ff33.333.33.xml         ff33.333.33.xml
ff33.333.33.xml                                   ff33.333.33.xml

Be One with the Optimizer
TG

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2009-03-17 : 14:41:31

Thanks, vijayisonly and TG
perfect job.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-18 : 05:11:50

select id,reverse(substring(reverse(filepath),1,charindex('\',reverse(filepath))-1))as 'file' from @tbl1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources