title: In a DB2 SQL query, how can you replace leading zeroes in a string with spaces to maintain a fixed length?
body: <pre><code>1                                    
  ------------------
  YYY100.00XXX
  YYY025.00XXX
  YYY005.00XXX
  </code></pre>
  
  <p>^ The above selection results from a query that looks like this:</p>
  
  <pre><code>SELECT CAST(STRING1 AS CHAR(3)) ||
         CAST(DECIMAL1 AS CHAR(6)) ||
         CAST(STRING2 AS CHAR(3))
  FROM TABLE;
  </code></pre>
  
  <p>My problem is the "025.00" and "005.00" - I need it to be " 25.00" and "  5.00" respectively - the leading zero needs to be replaced with a space and not removed so that the query looks like this:</p>
  
  <pre><code>1                                    
  ------------------
  YYY100.00XXX
  YYY 25.00XXX
  YYY  5.00XXX
  </code></pre>
  
  <p>What would be the best way to go about this?</p>
  
  <p><em>edit: I'm on DB2 v8.2.2</em></p>