根据放置在特定位置的特定字符串更新行

Question

我必须进行sql查询，将/的第二次出现和/的第三次出现之间的特定文本更改为包含特定单词的所有行的特定文本。因此，让我们举个例子，下面是我用粗体表示的字符串，如上面所提到的：

/DUREN - RS7/TAMBURLAGER/RS7-TAMB-COVER-2/IMG_9140.JPG /OHTER IO/MAN-6 as 56/MACHINE4 4/pic 56.jpg Goebel/RS7-BARRIER-6/IMG_9141.JPG /DUREN -RS7/ /DUREN -RS7/光学Goebel/R-势垒-6/IMG_33.jpg Goebel/RS7-BARRIER-7/IMG_9143.JPG /DUREN -RS7/ /Cars/MaszynaAniaZawiera2/Elementzawiera1/IMG_0152 - Copy.JPG /Aanekoski Supercalander 2014/SC4开卷机操作员侧/SC4-LF2/IMG_2486.JPG Goebel/RS7-BARRIER-6/IMG_9142.JPG /DUREN -RS7/ Goebel/RS7-BARRIER-9/IMG_9148.JPG /DUREN -RS7/ /OHTER IO/MAN-6 as 56/MACHINE4 4/pic-11.jpg /OHTER IO/MAN-6 as 56/MACHINE1 1/pic3.jpg /Aanekoski Supercalander 2014/Supercalander 4传动侧/SC4-D-CL1/IMG_3769.JPG 2014/Electriccabinets/EC1-1/IMG_2745.JPG超香菜/Aanekoski Drive/SC5-D-F1-SW1/IMG_5304.JPG /Aanekoski Supercalander 2014/SC5Unwinder /Cars/MaszynaAniaZawiera2/Elementzawiera1/IMG_0148 - Copy.JPG

现在，让我们来看看，我只想找到字符串是MAN-6作为56，然后将其改为-11。

因此，基于前面提到的示例，我们得到了包含它的3行：

/OHTER IO/MAN-6 as 56/MACHINE4 4/pic 56.jpg /OHTER IO/MAN-6 as 56/MACHINE4 4/pic-11.jpg /OHTER IO/MAN-6 as 56/MACHINE1 1/pic3.jpg

因此，最后，根据示例，这些行应该更改为：

IO/WHATEVER-11/MACHINE4/pic /OHTER 56.jpg /OHTER IO/WHATEVER-11/MACHINE4/pic-11.jpg IO/WHATEVER-11/MACHINE1/pic /OHTER 3.jpg

如何以安全的方式实现这一点，因为我获得了数以百万计的记录，而这必须是足够安全的，不能碰任何应该触及的东西？

提前谢谢。

进一步讨论的：

 select 
    stuff([PicturePath], charindex('/', [PicturePath], charindex('/', [PicturePath]) + 1) + 1, 13, 'WHATEVER-11') as new_data
from [WojtGroup].[dbo].[tbElemPics]
where substring([PicturePath], charindex('/', [PicturePath], charindex('/', [PicturePath]) + 1) + 1, 14) = 'MAN-SIX as 56/'

Answer 1

就像这样，

DECLARE @string      VARCHAR(100)=N'/OHTER IO/MAN-SIX as 56/MACHINE4/pic 56',
        @replacewith VARCHAR(100)=N'WHATEVER-11'

SELECT Substring(@string, Charindex('/', @string, Charindex('/', @string)+1)
                          + 1, Charindex('/', @string, Charindex('/', @string, Charindex('/', @string)+1)
                                                       + 1) - Charindex('/', @string, Charindex('/', @string) + 1) - 1) AS substring
SELECT @string                                                                                                                    AS ActualData,
       Stuff(@string, Charindex('/', @string, Charindex('/', @string)+1)
                      + 1, Charindex('/', @string, Charindex('/', @string, Charindex('/', @string)+1)
                                                   + 1) - Charindex('/', @string, Charindex('/', @string) + 1) - 1, @replacewith) AS ReplacedData

准备好尝试使用此select测试您的场景。

select Stuff(PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                                      + 1, Charindex('/', PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                                                                      + 1) - Charindex('/', PicturePath, Charindex('/', PicturePath) + 1) - 1, 'WHATEVER-11')
                                                                      from tbElemPics
WHERE  Substring(PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                             + 1, Charindex('/', PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                                                             + 1) - Charindex('/', PicturePath, Charindex('/', PicturePath) + 1) - 1) = 'MAN-SIX as 56'         

使用此更新语句

UPDATE tbElemPics
SET    PicturePath = Stuff(PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                                      + 1, Charindex('/', PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                                                                      + 1) - Charindex('/', PicturePath, Charindex('/', PicturePath) + 1) - 1, 'WHATEVER-11')
WHERE  Substring(PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                             + 1, Charindex('/', PicturePath, Charindex('/', PicturePath, Charindex('/', PicturePath)+1)
                                                             + 1) - Charindex('/', PicturePath, Charindex('/', PicturePath) + 1) - 1) = 'MAN-SIX as 56' 

Answer 2

您可以使用 和 来完成这一任务；

选择语句sqlfiddle

select 
    stuff(data, charindex('/', data, charindex('/', data) + 1) + 1, 13, 'WHATEVER-11') as new_data
from tbl
where substring(data, charindex('/', data, charindex('/', data) + 1) + 1, 14) = 'MAN-SIX as 56/'

update语句sqlfiddle

update tbl
set data = stuff(data, charindex('/', data, charindex('/', data) + 1) + 1, 13, 'WHATEVER-11')
where substring(data, charindex('/', data, charindex('/', data) + 1) + 1, 14) = 'MAN-SIX as 56/'