CODEWITHSUNDEEP
sqloracle-databasetrimregex-negation
Marium
Marium

Reputation: 253

ORACLE SQL Trim ineffective with Regular Expressions

CLARIFICATION

When I simplify the query, i.e. 

   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'))

I get

enter image description here

But if I incorporate into my actual query for 164,000+ distinct rows, I get

enter image description here

But I expect this (only difference is, Creation Date must have proper Date format, not this complex string)

enter image description here

ORIGINAL QUESTION

I am reading a date-time from an external Oracle Database, and have trimmed any extra spaces successfully with TRIM(I.CREATIONDATE).

I have verified this because my SQL query only displays distinct date values

However, TRIM(I.CREATIONDATE) turns 2/26/2019 11:05:44 AM into 26-FEB-19 11.05.43.925000 AM, but I only want 26-FEB-19

When I apply regular expression to only get date, i.e. REGEXP_SUBSTR(TRIM(I.CREATIONDATE),'[^ ]+'), it certainly outputs 26-FEB-19, but somehow extra spaces are added because I get duplicate dates.

I have tried applying TRIM a second time, i.e. TRIM(REGEXP_SUBSTR(TRIM(I.CREATIONDATE),'[^ ]+')), but I still get duplicates

Then I tried running regular expression first, then trim, but this does not work, i.e. TRIM(REGEXP_SUBSTR(I.CREATIONDATE,'[^ ]+')) still gives duplicates.

Please assist

Upvotes: 0

Views: 833

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think you want trunc(), not trim() to remove the time component.

So try:

trunc(i.creationdate)

Or if you want the days in a particular string representation:

to_char(i.creationdate, 'YYYY-MM-DD')

Upvotes: 1

Gen Wan
Gen Wan

Reputation: 2009

Try to_char:

trim(to_char(column_name,'dd-mon-yy'))

Upvotes: 1

Related Questions