如何替换/删除表列中字符串的一个很长的部分？
如何替换/删除表列中字符串的一个很长的部分？
提问于 2021-04-21 15:08:38
今天上午，我发现我的Server 2016数据库中的一个列以某种方式损坏了。
我有大约900行，有一个奇怪的字符串的变化：
<p>PC Gaming is central part of youth culture these days.</p><p> Let's examine this photo.</p> <img src="data:image/tiff;base64,GHFFFIFRHR////雙節棍/////兩節///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////ZBRE/////RE//VWKYH//////////////////////当遇到这样的值时，使用此方法的web应用程序就会崩溃。
因此，我正在寻找一种方法，从我的表中删除字符串。
我看到的唯一模式是，它们都以<img src="data:image/tiff;base64开头，最后至少有两个斜线，如.//
字符的数量是可变的，从20个一直到5000！
表列的类型为nvarchar(MAX)。
我知道如何替换的唯一方法是这样的：
UPDATE myTable
SET myColumn = REPLACE(myColumn, '<img src="data:image/tiff;base64', '')
WHERE someColumn LIKE '%<img src="data:image/tiff;base64'但这只会取代字符串的<img src="data:image/tiff;base64部分。
还有别的方法来替代这种东西吗？
谢谢!
以下是一些示例数据：
INSERT INTO myTable(myText) VALUES('<p>The efficiency we have at removing trash has made creating trash more acceptable.</p><img src="data:image/tiff;base64,GHFFFIFRHR////雙節棍/////軚垂直升降連鐳///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////ZBRE/////RE//VWKYH//////////');
INSERT INTO myTable(myText) VALUES('<p>Edith could decide if she should paint her teeth or brush her nails.</p><img src="data:image/tiff;base64,GGHRTHTRH////鐳射洗衣/////兩節///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////FGSS/////RE//GNDRG////');
INSERT INTO myTable(myText) VALUES('<p>Before he moved to the inner city, he had always believed that security complexes were psychological.</p><img src="data:image/tiff;base64,VNDFGSTGV////雙節棍/////軚垂直升降連鐳///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////ZBRE/////RE//JKSEFSE//////////////');
INSERT INTO myTable(myText) VALUES('<p>The rain pelted the windshield as the darkness engulfed us.</p><img src="data:image/tiff;base64,HJHNDFGFGX////鐳射洗衣/////兩節///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////JKYR/////RE//RTYGHTR//////////////////////');
INSERT INTO myTable(myText) VALUES('<p>The ants enjoyed the barbecue more than the family.</p><img src="data:image/tiff;base64,GDFGSFEFG////雙節棍/////軚垂直升降連鐳///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////XCWED/////RE//SDFRHUK//////////');
INSERT INTO myTable(myText) VALUES('<p>That was how he came to win $1 million.</p><img src="data:image/tiff;base64,ERQWDWJMJKL////鐳射洗衣/////兩節///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////HTHS/////RE//SDFFGRR///////');
INSERT INTO myTable(myText) VALUES('<p>The Guinea fowl flies through the air with all the grace of a turtle.</p><img src="data:image/tiff;base64,HHJYJHCBF////雙節棍/////兩節///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////OLIO/////RE//GEWFSDF/////////////');
INSERT INTO myTable(myText) VALUES('<p>She found his complete dullness interesting.</p><img src="data:image/tiff;base64,FGHGBEDRGBG////雙節棍/////軚垂直升降連鐳///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////SFAW/////RE//WERHHEER//////////////');
INSERT INTO myTable(myText) VALUES('<p>His son quipped that power bars were nothing more than adult candy bars.</p><img src="data:image/tiff;base64,GNFGJHRYN////鐳射洗衣/////兩節///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////DSFQ/////RE//DFWERH///////////////////');
INSERT INTO myTable(myText) VALUES('<p>The beauty of the sunset was obscured by the industrial cranes.</p><img src="data:image/tiff;base64,BNFHNN BN////雙節棍/////軚垂直升降連鐳///武術緣分風油/769P/++vb//vv3//77+P/軚垂直升降連鐳射洗衣乾衣腐蝕性氣墊毛筆//////如染色质免疫共沉淀芯片技术/武術緣分風油軚////////垂直升降連鐳射洗衣乾衣腐蝕性氣墊//////REQW/////RE//FDSDFSDF////////////');对于第1行，我需要删除或替换字符串"
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-04-21 15:56:40
请尝试以下解决方案。
我假设不需要的部分总是在列值的末尾。
如果一切正常，那么很容易将其转换为UPDATE语句。
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, htmldata NVARCHAR(MAX));
INSERT INTO @tbl (htmldata) VALUES
(N'<p>PC Gaming is central part of youth culture these days.</p><p>Let''s examine this photo.</p><img src="data:image/tiff;base64,GHFFFIFRHR////雙節棍////"/>'),
(N'<p>Edith could decide if she should paint her teeth or brush her nails.</p><img src="data:image/tiff;base64');
-- DDL and sample data population, end
DECLARE @searchFor NVARCHAR(40) = N'<img src="data:image/tiff;base64';
SELECT *
, LEFT(htmldata, CHARINDEX(@searchFor, htmldata) - 1)
FROM @tbl;Stack Overflow用户
发布于 2021-04-21 16:19:59
@Yitzhak Khabinsky击败了我，这很好，因为我误解了这一要求。无论如何，我将包括我的解决方案，因为它演示了如何从字符串中清除汉字，而将其余的保持不变。使用的函数是PatReplace8k。
--==== Using this parameter, all alphanumberic characters, spaces, dots, etc
-- are preserved, the rest are removed
DECLARE @cleanup VARCHAR(50) = '[^a-zA-Z0-9;,.:="<> ]'; -- Custom cleanup string
-- UPDATE mytable SET myText = CONCAT(s.S1,pr.newString)
SELECT NewString = CONCAT(s.S1,pr.newString)
FROM mytable AS t
CROSS APPLY (VALUES(CHARINDEX('<img src=', t.myText), LEN(t.myText))) AS f(Idx,Ln)
CROSS APPLY (VALUES(SUBSTRING(t.myText,0,f.Idx),
SUBSTRING(t.myText,f.Idx,LEN(t.mytext)))) AS s(S1,S2)
CROSS APPLY patreplace8k(s.S2,'[^a-zA-Z0-9;,.:="<> ]','') AS pr;返回：
NewString
--------------------------------------------------------------------------------------------------------------------------------------------------------
<p>The efficiency we have at removing trash has made creating trash more acceptable.</p><img src="data:imagetiff;base64,GHFFFIFRHR769Pvbvv377PZBREREVWKYH
<p>Edith could decide if she should paint her teeth or brush her nails.</p><img src="data:imagetiff;base64,GGHRTHTRH769Pvbvv377PFGSSREGNDRG
<p>Before he moved to the inner city, he had always believed that security complexes were psychological.</p><img src="data:imagetiff;base64,VNDFGSTGV769Pvbvv377PZBREREJKSEFSE
<p>The rain pelted the windshield as the darkness engulfed us.</p><img src="data:imagetiff;base64,HJHNDFGFGX769Pvbvv377PJKYRRERTYGHTR
<p>The ants enjoyed the barbecue more than the family.</p><img src="data:imagetiff;base64,GDFGSFEFG769Pvbvv377PXCWEDRESDFRHUK
<p>That was how he came to win $1 million.</p><img src="data:imagetiff;base64,ERQWDWJMJKL769Pvbvv377PHTHSRESDFFGRR
<p>The Guinea fowl flies through the air with all the grace of a turtle.</p><img src="data:imagetiff;base64,HHJYJHCBF769Pvbvv377POLIOREGEWFSDF
<p>She found his complete dullness interesting.</p><img src="data:imagetiff;base64,FGHGBEDRGBG769Pvbvv377PSFAWREWERHHEER
<p>His son quipped that power bars were nothing more than adult candy bars.</p><img src="data:imagetiff;base64,GNFGJHRYN769Pvbvv377PDSFQREDFWERH
<p>The beauty of the sunset was obscured by the industrial cranes.</p><img src="data:imagetiff;base64,BNFHNN BN769Pvbvv377PREQWREFDSDFSDF页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/67198545
复制相关文章
相似问题
腾讯云开发者
