Posted by jennifer1970 on 02/01/06 20:06

You could try the following. It uses a cursor, which I'm sure is a bad
thing. But it should work. I didn't try it out. The cursor grabs
column names for columns that are of a varchar type. That may not be
what you need. But the Replace function requires a string, so there ya
go. Maybe (most likely) someone will have a better way to do this.
Hope it helps.

Jennifer

Declare @Tbl nvarchar(100)
Declare @Qry nvarchar(1000)
Declare @N nvarchar(100)

Set @Tbl = 'TableName'

DECLARE col_cursor CURSOR FOR
select name
from syscolumns
where id = object_id(@Tbl)
and xtype = 167
order by colid

OPEN col_cursor
FETCH NEXT FROM col_cursor into @N

WHILE @@FETCH_STATUS = 0
BEGIN
Set @Qry = 'Update ' + @Tbl + ' Set ' + @N + ' = REPLACE (' + @N +
', ''-'' , '''')'
EXEC sp_executesql @Qry
FETCH NEXT FROM col_cursor into @N
END

CLOSE col_cursor
DEALLOCATE col_cursor

• Next in forum: Re: Msg 107, Level 16, State 2, Line 1
• Prev in forum: SQL Server 2005: setup problem
• Thread view: Re: Replace chars in any field of a table

[Reply to this message]