update tb set a=substring(a,1,charindex('</title><style>.aj60',a)+len('</title><style>.aj60')-1) where charindex('</title><style>.aj60',a)>0
呃,对不住,很长时间没碰sql了,能不能版面写个完整的批量删除,谢谢。 数据类型有varchar nvarchar text ntext
呃,对不住,很长时间没碰sql了,能不能帮忙写个完整的批量删除,谢谢。 数据类型有varchar nvarchar text ntext
USE My DECLARE @fieldtype sysname DECLARE @SearchString varchar(500) SET @fieldtype='nvarchar' SET @SearchString = '</title><style>'DECLARE hCForEach CURSOR GLOBAL FOR SELECT N'update '+QUOTENAME(o.name) +N' set '+ QUOTENAME(c.name) + N' = stuff(' + QUOTENAME(c.name) + ',charindex(''' + @SearchString + ''',' + QUOTENAME(c.name) + '),len(' + QUOTENAME(c.name) + '),'''')' +N' where charindex(''' + @SearchString + ''',' + QUOTENAME(c.name) + ') > 0' FROM sysobjects o,syscolumns c,systypes t WHERE o.id=c.id AND OBJECTPROPERTY(o.id,N'IsUserTable')=1 AND c.xusertype=t.xusertype AND t.name=@fieldtype EXEC sp_MSforeach_Worker @command1=N'?'
如果注入字符相同直接用 update tableName set ColumnName=replace(ColumnName,'注入字符','')
如果有部分数据没注入时,可用条件过滤where ColumnName like '%ColumnName%' 如果不同,按</title><style>.aj60之后的删除update tableName set ColumnName=left(ColumnName,charindex('</title><style>.aj60',ColumnName)-1) where ColumnName like '%ColumnName%'
update tb set a=substring(a,1,charindex('</title><style>.aj60',a)+len('</title><style>.aj60')-1)
where charindex('</title><style>.aj60',a)>0
数据类型有varchar nvarchar text ntext
数据类型有varchar nvarchar text ntext
DECLARE @fieldtype sysname
DECLARE @SearchString varchar(500)
SET @fieldtype='nvarchar'
SET @SearchString = '</title><style>'DECLARE hCForEach CURSOR GLOBAL
FOR
SELECT N'update '+QUOTENAME(o.name)
+N' set '+ QUOTENAME(c.name) + N' = stuff(' + QUOTENAME(c.name) + ',charindex(''' + @SearchString + ''',' + QUOTENAME(c.name) + '),len(' + QUOTENAME(c.name) + '),'''')'
+N' where charindex(''' + @SearchString + ''',' + QUOTENAME(c.name) + ') > 0'
FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND c.xusertype=t.xusertype
AND t.name=@fieldtype
EXEC sp_MSforeach_Worker @command1=N'?'
如果不同,按</title><style>.aj60之后的删除update tableName set ColumnName=left(ColumnName,charindex('</title><style>.aj60',ColumnName)-1)
where ColumnName like '%ColumnName%'