Find and Replace in all tables in text fields

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Allister Reid SSCrazy Points: 2666 More actions · Answer 1

Hi,

I used this to get rid of all £ signs out of data, you should be able to revise it for your purposes.

DECLARE @TableName VARCHAR(255)

SELECT @TableName = 'TableName'

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 'UPDATE ' + QUOTENAME(@TableName) + '

SET

'

SELECT @sql = @sql + ColName + ' = REPLACE(' + ColName + ', ''£'', ''''),

'

FROM

(

SELECT syscolumns.name as ColName

FROM sysobjects

JOIN syscolumns ON sysobjects.id = syscolumns.id

JOIN systypes ON syscolumns.xtype = systypes.xtype

WHERE

sysobjects.name = @TableName

AND systypes.name = 'text'

) Cols

SELECT @sql = STUFF(@Sql, LEN(@Sql) - 3, 1, '')

PRINT (@Sql)

--uncomment following line when you are happy with output

--EXEC (@Sql)

///Edit commented out exec

norbertackerman SSC Eights! Points: 945 More actions · Answer 2

How would I run it against all tables without specifying each one?

norbertackerman SSC Eights! Points: 945 More actions · Answer 3

I found a solution here:

Cookies on SQLServerCentral