[sql]
CREATE procedure tableReplace

@tableName varchar(100),
@searchExpression varchar(100),
@replaceExpression varchar(100)
as

----------------------------------------------------------------------------------------
--
-- Author: J.R.Sansom (john.sansom@bcs.org)
-- Description: An extension of the SQL "Replace" statement to apply the feature
-- table wide. This procedure is very useful for cleaning data,
-- such as removing quotes, carriage returns, commas etc.
--
-- Requirements: - Requires tableName be specififed.
-- - Requires that the search expression be defined.
-- - Requires that the replace expression be defined.
--
-- Example Execution
--
-- exec tableReplace
-- @tableName = 'TABLE_NAME,
-- @searchExpression = 'char(13)',
-- @replaceExpression = '~'
--
--
-- Hint: Find ASCII Lookup tables at http://www.lookuptables.com/
--
----------------------------------------------------------------------------------------



set nocount on

--Delcare the variables for use with this procedure
declare @SQLText varchar(8000)
declare @ColumnName varchar(100)

--Create a cursor to cycle through all of the columns.
declare cXursor cursor for
select column_name
from information_schema.columns
where table_name=@tableName
order by ordinal_position
open cXursor
fetch next
from cXursor
into @ColumnName


--Loop through the exectuion phase while a "next" cursor result is available.
while @@fetch_status=0
begin
--Creat the SQL query to apply the update statement to the current column
set @SQLText=' update ' + @tableName +
' set ' + @ColumnName + ' = replace( ' + @ColumnName + ' , ' +
@searchExpression + ' ,''' +
@replaceExpression + ''') '
--Execute the generated query.
exec (@SQLText)

--Inform the user of progress.
print 'Column ' + @columnName + ' updated sucessfully.'

fetch next
from cXursor
into @ColumnName
end

--Free up the resource taken by the cursor.
close cXursor
deallocate cXursor

go
[/sql]