Re: Help Extracting Date

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

Your question demonstrates there is no input validation from the piece of software you call 'application'. As there is no is_date function (or you would need to write one), and it is impossible to cater for all exceptions, judging the code above is fully unreadable due to hardcoded literals, I would strongly recommend dumping this crap. You should never ever concatenate multiple columns into one, as it is always easier to get concatenated output, as opposed to 'parse' the string provided. That would only work if you wrote a true BNF compatible parser for this string. But you are working with a RDBMS, and rright now the 'application' is misusing your RDBMS as a flat file system.
Get that trash can and dump that piece of shit.

-- 
Sybrand Bakker
Senior Oracle DBA