Read file's name only from file path

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

SQL Server 2005 Forums

Transact-SQL (2005)

Read file's name only from file path

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-05-10 : 17:20:40

A column named as filepath in which stored files' path.
For example,
d:\temp\myorder\aaa.pdf
d:\temp\myorder\bbb.pdf
...
How to code to read from filepath and then insert into another column filename? That is
aaa
bbb
...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-05-10 : 17:23:47

You don't want the extension? What happens if a file is named aaa.xxx.jjj.pdf?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-11 : 03:22:09

Start with this

declare @s varchar(100)
set @s='d:\temp\myorder\aaa.pdf'
select right(@s,charindex('\',reverse(@s))-1) as [file_name]

Madhivanan

Failing to plan is Planning to fail

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2010-05-12 : 08:53:35

Thank you.
Based on Madhivanan's code, I modified it as below and got file name:

select left((right(@s,charindex('\',reverse(@s))-1)), LEN(right(@s,charindex('\',reverse(@s))-1))-4)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-12 : 22:00:55

If the name has not any dot character in it (else the extension) you can use this alternate also:


declare @s varchar(100)
set @s='d:\temp\myorder\2aaa1.pdf'

SELECT PARSENAME(STUFF(@s,1,LEN(@s)-CHARINDEX('\', REVERSE(@s))+1,''),2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources