SQL replace statement using variables
<p>I would like some advice on how to use the SQL replace statement to clean up data with new words that are held in a two columns in separate table. </p>

<p>I am trying to use dynamic SQL and variables to generate the replace statement as below:</p>

<pre><code>DECLARE @ReplacementText VARCHAR(max) = 'SELECT UnwantedData FROM Table'

DECLARE @sql VARCHAR(8000) = 'SELECT '

SELECT 
  @sql = @sql + 
  ' @ReplacementText = replace(@ReplacementText, ''' + OldWord + ''', ' + NewWord + ')'
    + ' ,'
FROM ReplacementWords

PRINT @sql

EXEC(@sql)
</code></pre>

<p>This is a sample of what I get:</p>

<blockquote>
  <p>@ReplacementText = replace(@ReplacementText, ' “Saiko Maru”',  SAIKO MARU).</p>
</blockquote>

<p>plus the error: </p>

<blockquote>
  <p>Must declare the scalar variable "@ReplacementText"</p>
</blockquote>

<p>Grateful for any tips on how to correct what I am doing or a better way of going about this.</p>