Re: Help Extracting Date

nosbush_at_gmail.com wrote:
> On Jan 3, 4:40 pm, "sybrandb" <sybra..._at_gmail.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 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.

[]
> > > Thank you in advance for your help!
> > > GrantYour 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
>
> I agree with you completely. This wasn't setup wisely. However, I'm
> in the middle and have to work with what I have. Is there any way to
> put error checking in the code so that when an error is produced, a
> null date value is returned?

Well then you will have to go to PL/SQL. You will never be able to produce maintainable code in straight SQL to deal with this.

Lobby hard for correcting that table's lack of design. It is not worth the hassles of coding around it. This date field is only the beginning of your problems.

   Ed Received on Wed Jan 03 2007 - 22:08:42 CST