title: ORACLE SQL Trim ineffective with Regular Expressions
body: <h3>CLARIFICATION</h3>
  
  <p>When I simplify the <a href="https://stackoverflow.com/questions/56027804/oracle-sql-group-by-still-giving-duplicates?noredirect=1#comment98701230_56027804">query</a>, i.e. </p>
  
  <pre><code>   SELECT TO_CHAR(MIN(I.INCIDENTID))               AS "Incident ID",
            TRIM(TO_CHAR(I.CREATIONDATE,'DD-MON-YYYY'))           AS "Creation Date"    
     FROM   INCIDENT I  
     GROUP BY TRIM(TO_CHAR(I.CREATIONDATE,'DD-MON-YYYY'))
  </code></pre>
  
  <p>I get</p>
  
  <p><a href="https://i.stack.imgur.com/f29jt.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f29jt.png" alt="enter image description here"></a></p>
  
  <p>But if I incorporate into my actual query for 164,000+ distinct rows, I get</p>
  
  <p><a href="https://i.stack.imgur.com/1JvYL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1JvYL.png" alt="enter image description here"></a></p>
  
  <p>But I expect this (only difference is, Creation Date must have proper Date format, not this complex string)</p>
  
  <p><a href="https://i.stack.imgur.com/z9bvI.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/z9bvI.png" alt="enter image description here"></a></p>
  
  <h3>ORIGINAL QUESTION</h3>
  
  <p>I am reading a date-time from an external Oracle Database, and have trimmed any extra spaces successfully with <code>TRIM(I.CREATIONDATE)</code>.</p>
  
  <p>I have verified this because my SQL query only displays distinct date values</p>
  
  <p>However, <code>TRIM(I.CREATIONDATE)</code> turns <code>2/26/2019 11:05:44 AM</code> into <code>26-FEB-19 11.05.43.925000 AM</code>, but I only want <code>26-FEB-19</code> </p>
  
  <p>When I apply regular expression to only get date, i.e. <code>REGEXP_SUBSTR(TRIM(I.CREATIONDATE),'[^ ]+')</code>, it certainly outputs <code>26-FEB-19</code>, but somehow extra spaces are added because I get duplicate dates.</p>
  
  <p>I have tried applying <code>TRIM</code> a second time, i.e. <code>TRIM(REGEXP_SUBSTR(TRIM(I.CREATIONDATE),'[^ ]+'))</code>, but I still get duplicates</p>
  
  <p>Then I tried running regular expression first, then trim, but this does not work, i.e. <code>TRIM(REGEXP_SUBSTR(I.CREATIONDATE,'[^ ]+'))</code> still gives duplicates.</p>
  
  <p>Please assist</p>