Removing unknown repeated characters from SQL Database
/*
If there are fixed number of characters, Replace function can be used to replace multiple characters with a single one. But if you do not know number of characters duplicated, triplicated etc.. I used the code below to clean these multiple chars to make one.
I had this problem while i was transferring xml dataset through the web services and due to a mistake the escape characters were repeated each time data moved from one to other end. I practically used the script below to clean these unwanted duplicated characters.
*/
-- replace multiple characters with single character
-- in this example i am removing duplicate & (not only duplicate but also multiple &)
declare @dbName as varchar(100) = 'YOUR_DATABASE_NAME'
declare @tableName as varchar(100)= 'YOUR_TABLE_NAME'
declare @mulChar as varchar(1)='&' -- multiple characters you want to remove and replace with single
if object_id('tempdb.dbo.#fldList') is not null
drop table #fldList
create table #fldList (tblname varchar(200), fldname varchar(max))
insert into #fldList
exec ('select ''' + @tableName + ''', a.name
from ' + @dbName + '.sys.columns a
join ' + @dbName + '.sys.tables b
on a.object_id = b.Object_id
where b.name = ''' + @tablename + '''
and
system_type_id in
( select system_type_id from ' + @dbname + '.sys.types
where name in
(''text'', ''ntext'', ''char'', ''nchar'', ''varchar'', ''nvarchar'', ''xml'')
)
order by a.Column_id'
)
declare @tblName varchar(200), @fldName varchar(200)
declare @loopString as varchar(max) = ''
DECLARE curTable CURSOR FOR SELECT tblname, fldname FROM #fldList
OPEN curTable
FETCH NEXT FROM curTable INTO @tblName, @fldName
WHILE @@FETCH_STATUS=0
BEGIN
set @loopString = 'while exists (select ' + @fldName + ' from ' + @dbName + '.dbo.' + @tblName
+ ' where ' + @fldName + ' like ''%' + @mulChar + @mulchar + '%'')
begin
update ' + @dbname + '.dbo.' + @tblName + ' set ' + @fldName
+ ' = replace(' + @fldName + ',''' + @mulChar + @mulChar + ''', ''' + @mulChar + ''')
where ' + @fldName + ' like ''%' + @mulChar + @mulChar + '%''
end'
print (@loopstring)
exec(@loopstring)
FETCH NEXT FROM curTable INTO @tblName, @fldName
END
CLOSE curTable
DEALLOCATE curTable