title: DB2 SQL to extract a string before and after a space or a character
body: <p>I have a string which has spaces in full name. I want to extract first and last name.</p>
  <p>For example, the displayname has firstname middlename lastname:</p>
  <pre><code>John D Cooper 
  Mary L Finch 
  </code></pre>
  <p>I want to get the result as shown below.</p>
  <pre><code>JohnCooper
  MaryFinch
  </code></pre>
  <p>I am using passthrough to get into DB2 database and don't exactly know which function to use for that.</p>
  <p>I use scan function in PROC SQL to get the result.</p>
  <pre><code>proc sql;
    create table want as 
    select (scan(displayname,1,' ')||scan(displayname,-1,' ') as fullname 
    from have 
  ;
  quit;
  </code></pre>
  <p>I appreciate any help with this.
  Thanks in advance.</p>