TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
Superna Parajuli
NA
1
31.9k
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
Reply
Answers (
0
)
select
how to strore image in db and retrive in gridview