使用 t-sql 重命名磁盘上的文件 [英] Rename files on disk with 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 重命名磁盘上的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！