Re: Help Extracting Date

On Jan 4, 6:07 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> nosb..._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 ...
> > GrantGrant,
>
> 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

Thanks John! Received on Thu Jan 04 2007 - 08:32:29 CST