Superna Parajuli

Superna Parajuli

  • NA
  • 1
  • 31.8k

Removing unknown repeated characters from SQL Database

Dec 7 2012 6:42 PM

/*
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