File syntax question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

File syntax question

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2006-09-04 : 12:29:39

Afternoon,

I have a great stoerd proc that I found that almost does exactly what I need. Except I need it to say give me all files that start with doors_IR_todaysDate_*.txt


   -- This builds the file name for today
DECLARE @FileName VARCHAR(30)
  SET @FileName = 'doors_IR_' --Part of the file name you identified in first posting
                + REPLACE(
                    CONVERT(CHAR(8),GETDATE(),112) --Returns today's date as mm/dd/yy
                  ,'/','') --Replaces the slashes with nothing
                + '.txt' --Extension name for files you identified in first posting

How do I add "like" to this declare statement?

Thanks in advance.

Laura

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 12:35:52

Where do you want to search for the "files"?

In the database in a table?
In the file system?

Peter Larsson
Helsingborg, Sweden

lmayer4
Starting Member

33 Posts

Posted - 2006-09-04 : 13:38:58

the file system but I need something after the

'doors_IR_'+ REPLACE(CONVERT(CHAR(8),GETDATE(),112),'/','')

and before the

+ '.txt'

that is a wildcard. So that it picks up any file that starts with doors_IR_todaysDate and ends with .txt

Is that possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-04 : 15:58:05

something like this?

declare @temp varchar(300)

select	@temp = 'dir /b /a:hsra "c:\doors_IR_' + CONVERT(VARCHAR, GETDATE(), 112) + '_*.txt"'

create table #files (filename varchar(300))

insert	#files
exec	master..xp_cmdshell @temp

delete
from	#files
where	filename is null
	or filename = 'file not found'

select * from #files

drop table #Files

Peter Larsson
Helsingborg, Sweden

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-09-04 : 16:07:44

Excellent! Thanks so much I will try that.

Thanks again.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources