title: Difference between a REPLACE statement and WHERE when replaceing characters in a field?
body: <p>I apologize if this has been asked before. I have a field called <code>POSITION</code> which is <code>VARCHAR2(20 BYTE)</code>. If I use the statement </p>
  
  <pre><code>update SA_ASSET
  set POSITION = REPLACE(POSITION, 'SOLIDS BLDG', 'SOLIDS STRUCTURE')
  where location = 'SOUTH COUNTY REGIONAL AWT PLAN';
  </code></pre>
  
  <p>I come up with an </p>
  
  <blockquote>
    <p>ORA-12899: value too large for column "SYNERGEN"."SA_ASSET"."POSITION"
    (actual: 21, maximum: 20) error.</p>
  </blockquote>
  
  <p>If I do the same thing with a WHERE statement it works:</p>
  
  <pre><code>update SA_ASSET
  set POSITION = 'SOLIDS STRUCTURE'
  where location = 'SOUTH COUNTY REGIONAL AWT PLAN'
  AND POSITION = 'SOLIDS BLDG';
  </code></pre>
  
  <p>The length of the replacement is the same (15 chars and 15 bytes) but something is different using the REPLACE statement. This has happened on several occasions where the char/byte count has been less than 20. We are using Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production if that makes any difference Has anyone come across this and have an answer as to why?</p>