处理文章附件路径问题

 

/*
一共有375篇带有附件的，刚好这些文章的最大长度（7482）都不超过8000个字符，因此可以使用以下脚本处理
*/
DECLARE @id INT
DECLARE @content VARCHAR(8000)

DECLARE C CURSOR FOR
SELECT  --TOP 1
        ARTICLE_ID ,
        --AR_CONTENT ,
        CAST(AR_CONTENT AS VARCHAR(8000)) AS content
        --,
        --LEN(CAST(AR_CONTENT AS VARCHAR(8000))) L
FROM    dbo.BAP_CMS_ARTICLE
WHERE   1 = 1
        --AND ARTICLE_ID = 145833
        AND AR_CONTENT LIKE '%InstallDir_ChannelDir%'

OPEN C
FETCH NEXT FROM C INTO @id, @content
WHILE @@FETCH_STATUS = 0 
    BEGIN
        --PRINT @content
        SET @content = REPLACE(@content, '[InstallDir_ChannelDir]{$UploadDir}',
                               '/WebDeploy/UploadFiles')
        --PRINT @content
        UPDATE  BAP_CMS_ARTICLE
        SET     AR_CONTENT = @content
        WHERE   ARTICLE_ID = @id
        FETCH NEXT FROM C INTO @id, @content
    END
    
CLOSE C
DEALLOCATE C