本文介绍了使用 t-sql 重命名磁盘上的文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 T-SQL
I am using T-SQL
我在这里有几个 excel 文件:C:\MyFiles\
I have a few excel files located here: C:\MyFiles\
我想删除该目录中文件名中的所有撇号.
I want to remove all the apostrophes in the file names in that directory.
现在要删除撇号,人们会使用这样的代码.
Now to remove apostrophes one would use code like this.
update MyTable
set FileName= replace(FileName, '''', '')
如果我在数据库中有所有文件名,那么使用上面的代码就很容易了.但我需要更新位于磁盘上的文件名.
If I had all the file name in the DB it would be easy to do with the code above. But I need to update the file names that are located on disk.
我将如何使用 T-SQL 执行此操作?
How would I go about doing this with T-SQL?
它必须是 T-SQL,因为我需要将它添加到我的存储过程中的现有代码中.
It must be T-SQL because I need to add it to my existing code in my Stored Procedure.
推荐答案
SET NOCOUNT ON;
CREATE TABLE #FileList
(
FileID INT IDENTITY(1, 1)
,Line VARCHAR(512)
)
CREATE TABLE #temp
(
isFileThere BIT
,isDirectory BIT
,parentDirExists BIT
)
DECLARE @Command VARCHAR(1024)
, @RowCount INT
, @counter INT
, @FileName VARCHAR(1024)
, @FileExists BIT
SET @Command = 'dir C:\MyFiles\ /A-D /B'
PRINT @Command
INSERT #FileList
EXEC master.dbo.xp_cmdshell @Command
DELETE FROM #FileList
WHERE Line IS NULL
SELECT @RowCount = COUNT(*)
FROM [#FileList]
SET @counter = 1
WHILE ( @counter <= @RowCount )
BEGIN
SELECT @FileName = [Line]
FROM [#FileList]
WHERE [FileID] = @counter
SET @Command = 'C:\MyFiles\' + @FileName + ''
PRINT @Command
INSERT [#temp]
EXEC master.dbo.xp_fileExist @Command
SELECT @FileExists = [isFileThere]
FROM [#temp]
IF @FileExists = 1
AND CHARINDEX('''', @FileName) > 0
SET @Command = 'REN "C:\MyFiles\' + @FileName + '" "'
+ REPLACE(@FileName, '''', '') + '"'
ELSE
SET @Command = ''
SET @counter = @counter + 1
PRINT @Command
IF LEN(@Command) > 0
EXEC master.dbo.xp_cmdshell @Command
END
DROP TABLE #FileList
DROP TABLE [#temp]
这篇关于使用 t-sql 重命名磁盘上的文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!