SQLTeam.com | Weblogs | Forums

How to select the file using first two sets of numbers prefixed with filenames


#1

When i read the following filename "D:\MYJCC_Files\65472.242792-Consignación_GRA" from sql server in a windows folder, it is reading as below:

D:\MYJCC_Files\65472.242792-ConsignaciA3n_GRA.pdf

But the actual filename has special characters. i have like these stupid filenames close to 5k and now i need to rename and move file to D:\Targetfiles\ folder

how can i copy / rename above file as "65472.242792-Newfile.pdf"

Can you please tell me is there a way to pick the filename based on first two set numeric values, all files has unique front two set number values.

Thanks a lot for the helpful info.

This is how i am currently picking all files from folder, but those special characters are interpreted and getting replaced on sql server and having issue.

declare @datasetfiles table (ID int IDENTITY, Filename varchar(200), docfilename varchar(200), docfilepath varchar(250), docid varchar)
declare @files table (ID int IDENTITY, FileName varchar(500))
insert into @files execute xp_cmdshell 'dir D:\MYJCC_Files /b'

declare @datasetfiles1 table (ID int IDENTITY, [sFilename] varchar(200), sdocid varchar(20))
insert into @datasetfiles1([sFilename],sdocid)
--declare @Completefiles table (ID int IDENTITY, FileName varchar(500), sdocid varchar(20))
Select [FileName],
substring([FileName]
,charindex('.',[FileName]+'.')+1
,charindex('.'
,replace([FileName],'-','.')+'.'
,charindex('.',[FileName]+'.')+1
)
-charindex('.',[FileName]+'.')-1
)

from @files


#2

The following example.

declare @var varchar(128) = 'D:\MYJCC_Files\65472.242792-Consignación_GRA';
select 
	left(b,charindex('-',b+'-') ) + 'Newfile.pdf' as Newfilename
from
	( values( reverse(@var) ) ) A(a)
	cross apply (values ( reverse(left(a,charindex('\',a+'\')-1)) ) )B(b);