Select query filename if no file show none

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)

Select query filename if no file show none

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-10-12 : 10:50:23

I am using the following query to show the filenames, there there is no file name it show null, instead is it possible to say 'None'

SELECT REVERSE(LEFT(REVERSE(filename),CHARINDEX('\', REVERSE(filename),1)-1))
from TAB_Recipients
where ModuleRecordID = @ModuleID

Thank you very much for the helpful info.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 10:56:16

[code]
SELECT IsNull(Nullif(REVERSE(LEFT(REVERSE(filename),CHARINDEX('\', REVERSE(filename),1)-1)),Null),'None')
from TAB_Recipients
where ModuleRecordID = @ModuleID
[/code]

PBUH

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-10-12 : 11:28:44

Hello sachin, Thanks.

I have a small problem with the query, the filename field has the paths with the files you can see like this: 'c:\Myprojectfiles\myfile.pdf'

or it could be like this:
just the path upto folder without a filename at the end.
''c:\Myprojectfiles\' in these cases is there a way to put 'None'

Thank you very much for the helpful info.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 11:32:40

Hi,

Will the files always be .pdf ?

PBUH

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-10-12 : 11:46:45

Sachin it could be any file, not just .pdf
for that reason i am using this to just get the filename from teh whole path:
REVERSE(LEFT(REVERSE(filename),CHARINDEX('\', REVERSE(filename),1)-1))

for those records which does'nt have a file, but will have the filepath upto the folder., in those cases want to show just None, instead of null.

Right now currently it is showing null where there is no file at the end of the path.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 11:59:42

[code]
declare @pth varchar(40)='c:\Myprojectfiles\abc.pdf'
select case
charindex('.',REVERSE(LEFT(REVERSE(@pth),CHARINDEX('\', REVERSE(@pth),1)-1)) )
when 0 then 'None' else REVERSE(LEFT(REVERSE(@pth),CHARINDEX('\', REVERSE(@pth),1)-1)) end
[/code]

PBUH

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-10-12 : 13:04:35

Sachin, Thanks a LOT.

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 13:10:48

quote:
Originally posted by cplusplus

Sachin, Thanks a LOT.

Welcome.
But the query has a glitch.It wont work if any of the foldername in the path contains '.'

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources