0

I am trying to find how many people has their identity card expired from a database in witch the data has this format: 

CI+OT+1x78xx+POL.SLATINA+29-08-2003+14-07-2013

or 

BI+GV+591xxx+MUN CAMPIA TURZII+03-02-1997+

so it's delimited by + five times (some of them does not expire ever)

I've done this, but it don't works...

select 
set_act_id_1,
to_number(sysdate-to_date(substr(set_act_id_1,instr(set_act_id_1,'+',1,5)+1,length(set_act_id_1)),'DD-MM-YYYY')) 
from STAGE.CUSTOMERS 
where set_act_id_1 is not null 
group by set_act_id_1 
having sysdate-to_date(substr(set_act_id_1,instr(set_act_id_1,'+',1,5)+1,length(set_act_id_1)),'DD-MM-YYYY')<0;
Utsav
  • 7,914
  • 2
  • 17
  • 38
Ady Lacan
  • 59
  • 2
  • 7
  • 1) Which version of Oracle are you using? 2)So the first one is expired as `14-07-2013` is less than current date and second one will never expire as the 5th position after `+` is null? – Utsav Apr 26 '17 at 12:18
  • toad for oracle 12.10 – Ady Lacan Apr 26 '17 at 12:57

1 Answers1

2
SELECT set_act_id_1,
       TO_DATE( REGEXP_SUBSTR( set_act_id_1, '[^+]+', 1, 6 ), 'DD-MM-YYYY' )
FROM   STAGE.CUSTOMERS 
WHERE  set_act_id_1 is not null

Or

SELECT set_act_id_1,
       TO_DATE( SUBSTR( set_act_id_1, INSTR( set_act_id_1, '+', 1, 5 ) + 1 ), 'DD-MM-YYYY' )
FROM   STAGE.CUSTOMERS 
WHERE  set_act_id_1 is not null

Update:

SELECT CASE
         WHEN REGEXP_LIKE( expiry_date, '^\d{1,2}[ \/-](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[ \/-]\d{4}$', 'i' )
         THEN TO_DATE( expiry_date, 'dd mon yyyy' )
         WHEN REGEXP_LIKE( expiry_date, '^\d{1,2}[ \/-](JAN|FEB|MAR|APR|MAY|JUN|JUL|AUG|SEP|OCT|NOV|DEC)[ \/-]\d{2}$', 'i' )
         THEN TO_DATE( expiry_date, 'dd mon yy' )
         WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|[12]\d|3[01])[ \/-](0?[1-9]|1[0-2])[ \/-]\d{4}$' )
         THEN TO_DATE( expiry_date, 'dd mm yyyy' )
         WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|1[0-2])[ \/-](0?[1-9]|[12]\d|3[01])[ \/-]\d{4}$' )
         THEN TO_DATE( expiry_date, 'mm dd yyyy' )
         WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|[12]\d|3[01])[ \/-](0?[1-9]|1[0-2])[ \/-]\d{2}$' )
         THEN TO_DATE( expiry_date, 'dd mm yy' )
         WHEN REGEXP_LIKE( expiry_date, '^(0?[1-9]|1[0-2])[ \/-](0?[1-9]|[12]\d|3[01])[ \/-]\d{2}$' )
         THEN TO_DATE( expiry_date, 'mm dd yy' )
         ELSE NULL
       END AS expiry_date
FROM   (
  SELECT set_act_id_1,
         SUBSTR( set_act_id_1, INSTR( set_act_id_1, '+', 1, 5 ) + 1 ) As expiry_date
  FROM   STAGE.CUSTOMERS 
  WHERE  set_act_id_1 is not null
)
MT0
  • 143,790
  • 11
  • 59
  • 117
  • No, I have many formats for date, like PT+ROU+0521xx942+ILFOV+23-Sep-2013+23-Sep-2018 or CI+GG+37xx29+SPCJEP GIURGIU+09/DEC/2014+18/JUL/2074 or CI+XH+9525xx+SPCLEP ORADEA+25-JUL-2014+06-DEC-2024 I supose i need a function with 4 cases, but i don't know how to start... – Ady Lacan Apr 26 '17 at 12:56
  • for the second query: ORA-01858: a non-numeric character was found where a numeric was expected – Ady Lacan Apr 26 '17 at 12:59
  • @AdyLacan Updated – MT0 Apr 26 '17 at 13:09
  • ORA-01847: day of month must be between 1 and last day of month :( – Ady Lacan Apr 26 '17 at 13:12
  • @Seyran The OP appears to be getting the last `+` delimited value of the string - so all you need to do is find the position of the 5th `+` character and then take all of the substring following it. – MT0 Apr 26 '17 at 13:12
  • @AdyLacan Updated again - but if you have the date `01-02-2017` how do you know if it is the 1st February or 2nd January? – MT0 Apr 26 '17 at 13:19
  • @mt0 may be you mean that query must be like this `SELECT set_act_id_1, TO_DATE( SUBSTR(set_act_id_1, INSTR(set_act_id_1, '+', -1) + 1 ), 'DD-MM-YYYY' ) FROM STAGE.CUSTOMERS WHERE set_act_id_1 is not null`, in your query I cant understand this statement `INSTR( set_act_id_1, 1, 5 ) + 1 )` – Seyran Apr 26 '17 at 13:52
  • it must be substr(set_act_id_1,instr(set_act_id_1,'+',1,5)+1,length(set_act_id_1) with '+' – Ady Lacan Apr 26 '17 at 14:16
  • but even if I correct it, I have this Error: ORA-01839: date not valid for month specified – Ady Lacan Apr 26 '17 at 14:18
  • Some colegue told me to create a function (with 4 cases) and after thet to call it. Can someone help me? – Ady Lacan Apr 26 '17 at 14:18
  • @Seyran you were correct, when I'd cross typed it from the database server I'd missed a `'+',` from the code. Fixed it now. – MT0 Apr 26 '17 at 19:44
  • @AdyLacan http://stackoverflow.com/a/37660175/1509264 call it like this: `COALESCE( parse_Date( expiry_date, 'dd mon yy' ), parse_Date( expiry_date, 'dd mon yyyy' ), parse_Date( expiry_date, 'dd mm yy' ), ... )` with all the formats you wish to try to parse. – MT0 Apr 26 '17 at 19:47