SQL Server filter file name special characters (use SQL Server to modify the physical address name)

Tag:sql    Modify file name     get file directory    ren   xp_cmdshell    

declare @cmd nvarchar (a) Declare @messageid nvarchar (declare @serialid nvarchar) declare @oldFileName nvarchar ($) Declare @fileName nvarchar ($) Declare @filePath nvarchar (4000) declare @oldfilePath nvarchar (40 00)--Change path D: to d$ declare @newFilePath nvarchar (4000) Declare ccur cursor FOR select D.messageid,m.serialid,m.attachm Entname,m.attachmentpath from D left join R on D.messageid=r.messageid left join dataattachment m on M.messageid=d.messag EID where d.messageid= ' 0.abe3b3d58161ccb3de7ab63754bb3f15 '--find the required attachments via the database open CCur fetch ccur                    Into @messageid, @serialid, @fileName, @filePath while @ @fetch_status =0 begin --Determine if the attachment file name contains special characters if CHARINDEX (' + ', @filename) >0 or CHARINDEX (' $ ', @filename) >0 or CHARINDEX (' &am p; ', @filename) >0--contains +, $, & Begin set @[email prot                  Ected]     Set @filename =replace (@filename, ' + ', ') set @filename =replace (@filename, ' $ ', ') Set @filename =replace (@filename, ' & ', ')--filters These special characters, replaces the new file name set @newFilePath =dbo. Getdirectorypath (@filePath) + ' \ ' + @filename--New path name (<span style= "font-family:arial, Helvetica, Sans-serif;" >dbo. Getdirectorypath method to get the file directory according to the path </span><span style= "font-family:arial, Helvetica, Sans-serif;"                             >) </span>--old path name changed to disk drive set @oldfilePath =replace (@filePath, ' D: ', ' d$ ')                                              Set @oldfilePath = ' \\192.168.21.36\ ' + @oldfilePath--the attachment is under the remote server, if it is not remote, do not add a disk path --EXEC Master.dbo.xp_cmdshell ' net use \\<span style= "Font-family:arial, Helvetica, S Ans-serif; " >192.168.21.36</span><span style= "font-family:arial, Helvetica, Sans-serif;"       >\d$ "123456"/user: "192.168.21.36\administrator" </span>                --exec Master.dbo.xp_cmdshell ' ren \\<span style= "font-family:arial, Helvetica, Sans-serif;" >192.168.21.36</span><span style= "font-family:arial, Helvetica, Sans-serif;"    >\d$\book1.xls,book2.xls ' </span> Set @cmd = ' ren ' + @oldfilePath + ', ' + @filename + ' --patchwork requires your own commands--Modify the file physical location name (connect the remote server and the drive letter where the file name needs to be modified) exec Master.dbo.xp_cmdshell ' n   Et use \\192.168.21.36\d$ "123456"/user: "192.168.21.36\administrator" ' EXEC Master.dbo.xp_cmdshell                       The length of the @cmd [email protected] must be set to specific data, cannot be set to nvarchar (max), the "procedure requires a parameter of type ' varchar ' ' command_string '" error --Insert File name modify log table insert into Datafilenameupdatelog (messageid,oldfilename,newfilename,oldfilepath,n Ewfilepath) VALUES (@messageid, @oldfileName, @filename, @filePath, @newFilePath)- -Update the new file name and new path (Serialid is unique) according to Serialid UPDATe A set [email protected],[email protected] where [email protected]--insert A message reminder to me declare @content nvarchar (max) Set @content = ' Group communications attachment name contains special symbols, filtered, please check. Serialid: ' + @serialid + ', MessageID: ' + @messageid + ', original filename: ' + @oldfileName + ', new file name: ' + @filenam E + ' (System auto Send) ' exec aa.dbo. [Aaremind] 0, ', 11182, @content end fetch ccur into @messageid, @seria Lid, @fileName, @filePath end Close CCur deallocate CCur End

Gets the file directory dbo according to the path. Getdirectorypath method

--=============================================  --Author:        Paul Griffin  --Create Date:   January -  Description: Returns The   path without the file name-from  a full                path:  --                    D:\Temp\ Resources\images\my.picture.jpg--                ==> D:\Temp\Resources\Images  --=========================== ==================  CREATE FUNCTION [dbo]. [Getdirectorypath]  (      @Path NVARCHAR (max)  )  RETURNS NVARCHAR (max)  as  BEGIN      DECLARE @FileName NVARCHAR ( Max)      DECLARE @ReversedPath NVARCHAR (max)      DECLARE @PathLength INT         SET @ReversedPath = REVERSE (@Path)      SELECT @PathLength = CHARINDEX (' \ ', @ReversedPath)      SELECT @FileName = Left (@Path, LEN (@Path)-@PathLength)      RETURN @FileName  END  

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

SQL Server filter file name special characters (use SQL Server to modify the physical address name)