T-SQL answer.
Re: How can I perform a table wide replace statememt?
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 asremoving 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.
declarecXursor cursor FOR
selectcolumn_name
frominformation_schema.COLUMNS
wheretable_name=@tableName
orderby ordinal_position
opencXursor
fetchnext
fromcXursor
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.'
fetchnext
fromcXursor
INTO@ColumnName
end
--Free up the resource taken by the cursor.
closecXursor
deallocate cXursor
go
|
05-25-2006, 05:01 AM
