title: NVL, SUBSTR, and RPAD syntax
body: <p>I am trying to create a fixed width file by selecting the values like so:</p>
  
  <pre><code>SELECT distinct SubStr(RPad(InitCap(p.namelast),50,' '),1,50)
  || SubStr(RPad(InitCap(p.namefirst),50,' '),1,50)
  || SubStr(RPad(p.house ||' ' || p.street || ' '  ||p.streettype,50,' '),1,50)
  ||'                                                  ' 
  ||SubStr(RPad(InitCap(p.city),35,' '),1,35)
  ||NVL(SubStr(RPad(p.province,2,'0'),1,2),'  ')
  ||NVL(SubStr(RPad(p.postal,5,'0'),1,5),'     ')
  </code></pre>
  
  <p>The above works fine. However, I also need to account for the fact that the address will either be parsed in the fields I have above (p.house, p.street, p.streettype...) or be found in ADDRESS1 and ADDRESS2. ADDRESS 2 however contains city state and zip and will need to be parsed.  </p>
  
  <p>Can I accomplish this by using an NVL? I feel like if I use an NVL and SUBSTR the values out that I need, It could work. But I'm not sure of the syntax.  </p>
  
  <p>Data in ADDRESS1 and ADDRESS2 looks like this respectively:</p>
  
  <pre><code>123 boom drive|MONTGOMERY AL 45777
  </code></pre>