Re: Help Extracting Date

From: John K. Hinsdale <hin_at_alma.com>
Date: 4 Jan 2007 04:07:20 -0800
Message-ID: <1167912440.399754.246800@i80g2000cwc.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 ...
> Grant

Grant,

Here's some disgusting code to go along with your digusting data ;) The query below will TRY to "validate" your embedded fifth value, but depending on how "dirty" the data is it may still give you the ORA-01847 error. For example, a date like '39Jan2003' will get past the regex match and trip it up.

Comments from other posters that the database you have inherited is crappy and unmaintainable are right on. I do understand this is not your doing (and that you have some work to get done).

SELECT mdta AS original_mdta,

       /* Parse out 5th value in ;-delimited string
          and match it to date pattern */
       decode(regexp_instr(substr(mdta,
                           instr(mdta,';',1,4)+1,
                           instr(mdta,';',1,5) -

instr(mdta,';',1,4)-1),

'^[0-3]?[0-9][ADFJMNOS][aceopu][bcglnptvy][0-9]?[0-9]?[0-9][0-9]$',

                           1, 1, 0, 'i'), /* Case-insensitive */
              /* Does not match date pattern */
              null, to_date(null),
              0, to_date(null),
              /* Otherwise assume valid ddMonyyyy */
              to_date(substr(mdta,
                             instr(mdta,';',1,4)+1,
                             instr(mdta,';',1,5) -
instr(mdta,';',1,4)-1),
                      'ddMonyyyy'))
              AS ActionDate,
       'More Stuff' AS another_col

FROM example_for_grant

Here is the above query "in action" on some test data:

http://otb.alma.com/otb.fcgi?func=btable&server=orcl&user=ALMA&qid=22

Hope that helps.

John Hinsdale Received on Thu Jan 04 2007 - 06:07:20 CST