Re: Help Extracting Date

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 4 Jan 2007 07:07:14 -0800
Message-ID: <1167923233.941924.204260@42g2000cwt.googlegroups.com>

nosbush_at_gmail.com wrote:
> I am working with a table that has one of its columns setup to contain
> multiple pieces of data. For example, it contains
> username;entered_date;code1;code2;action_date;etc.... Everything is
> separated by semicolons. I need to get the data out and in separate
> columns which hasn't been a problem except for the 5th piece,
> action_date. I didn't set this table up nor do I have any way of
> changing how the data is entered; it is a little strange but this is
> what I have to work with. The code I'm working with to get the
> action_date is the following:
>
> DECODE(substr(mdt.mdta,instr(mdt.mdta,';',1,4)+1,(instr(mdt.mdta,';',1,5))-(instr(mdt.mdta,';',1,4))-1),
> '_', TO_DATE(null),
> TO_DATE(substr(mdt.mdta,instr(mdt.mdta,';',1,4)+1,(instr(mdt.mdta,';',1,5))-(instr(mdt.mdta,';',1,4))-1),
> 'ddMonyyyy')) ActionDate
>
> The problem is, I am getting error message ORA-01847: day of month must
> be between 1 and last day of month. The date format I use in the
> TO_DATE function is correct. While every row does have something in
> this column (always the username and entered_date) there isn't always
> an action_date. Sometimes it is blank or sometimes the user has
> entered '_' (not sure why but not much I can do about it). How can I
> rewrite this code so that it pulls and formats the date when it is
> present and ignores the rest?
>
> Thank you in advance for your help!
> Grant

A little bit of experimentation:
CREATE TABLE T1 (MDTA VARCHAR2(1000));

INSERT INTO T1 VALUES ('SAM;20060101;01;01;20060105');
INSERT INTO T1 VALUES ('SPADE;20060110;01;02;20060110');
INSERT INTO T1 VALUES ('JACK;20060112;01;03;');
INSERT INTO T1 VALUES ('BLACK;20060130;01;04;_');
INSERT INTO T1 VALUES ('MISSING;20060201;01;05');

Note the last three entries: the 03 entry has no date value, the 04 entry has an underscore for the date value, and the 05 entry completely excludes the ; and the date entry.

If we were to take this in steps, and first locate the end markers of each field in the column, the SQL statement would look like this: SELECT
MDTA,

  INSTR(MDTA||' ;;',';',1,1) POSITION_1,
  INSTR(MDTA||' ;;',';',1,2) POSITION_2,
  INSTR(MDTA||' ;;',';',1,3) POSITION_3,
  INSTR(MDTA||' ;;',';',1,4) POSITION_4,
  INSTR(MDTA||' ;;',';',1,5) POSITION_5

FROM
T1;

Note that I padded each MDTA column value with a space and two trailing semicolons at the end:
P_1 P_2 P_3 P_4 P_5 MDTA
4 13 16 19 29 SAM;20060101;01;01;20060105 6 15 18 21 31 SPADE;20060110;01;02;20060110 5 14 17 20 22 JACK;20060112;01;03; 6 15 18 21 24 BLACK;20060130;01;04;_ 8 17 20 24 25 MISSING;20060201;01;05 We could slide the above into an inline view to clean up the next step, but I will skip that step and use the values directly in the SQL statement.
SELECT
MDTA,
TRIM(SUBSTR(MDTA,1,INSTR(MDTA||' ;;',';',1,1)-1)) VALUE_1,

  TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,1)+1,INSTR(MDTA||'
;;',';',1,2)-INSTR(MDTA||' ;;',';',1,1)-1)) VALUE_2,
  TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,2)+1,INSTR(MDTA||'
;;',';',1,3)-INSTR(MDTA||' ;;',';',1,2)-1)) VALUE_3,
  TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,3)+1,INSTR(MDTA||'
;;',';',1,4)-INSTR(MDTA||' ;;',';',1,3)-1)) VALUE_4,

REPLACE(TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,4)+1,INSTR(MDTA||' ;;',';',1,5)-INSTR(MDTA||' ;;',';',1,4)-1)),'_',NULL) VALUE_5 FROM
T1;

Note that I am trimming each value returned, and replace an underscore in the final VALUE column with a NULL.

MDTA                          VALUE_1  VALUE_2  VALUE_3  VALUE_4
VALUE_5
SAM;20060101;01;01;20060105   SAM      20060101 01       01
20060105
SPADE;20060110;01;02;20060110 SPADE    20060110 01       02
20060110
JACK;20060112;01;03;          JACK     20060112 01       03
BLACK;20060130;01;04;_        BLACK    20060130 01       04
MISSING;20060201;01;05        MISSING  20060201 01       05

Finally, slide the above into an inline view, just to make it easier to test whether or not VALUE_5 contains a valid date. If VALUE_5 is not null, performing testing and convert to a date, otherwise return a null:
SELECT

  VALUE_1,
  VALUE_2,
  VALUE_3,
  VALUE_4,

  NVL2(VALUE_5,TO_DATE(VALUE_5,'YYYYMMDD'),NULL) VALUE_5 FROM
  (SELECT
    MDTA,
    TRIM(SUBSTR(MDTA,1,INSTR(MDTA||' ;;',';',1,1)-1)) VALUE_1,

    TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,1)+1,INSTR(MDTA||'
;;',';',1,2)-INSTR(MDTA||' ;;',';',1,1)-1)) VALUE_2,
    TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,2)+1,INSTR(MDTA||'
;;',';',1,3)-INSTR(MDTA||' ;;',';',1,2)-1)) VALUE_3,
    TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,3)+1,INSTR(MDTA||'
;;',';',1,4)-INSTR(MDTA||' ;;',';',1,3)-1)) VALUE_4,

REPLACE(TRIM(SUBSTR(MDTA,INSTR(MDTA||' ;;',';',1,4)+1,INSTR(MDTA||' ;;',';',1,5)-INSTR(MDTA||' ;;',';',1,4)-1)),'_',NULL) VALUE_5 FROM
T1);

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jan 04 2007 - 09:07:14 CST