Re: Select data prior to a character in a row?

On Jan 3, 4:41 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 3, 1:20 pm, DP <dxpe..._at_gmail.com> wrote:
>
>
>
>
>
> > On Jan 3, 1:04 pm, Walt <walt_ask..._at_SHOESyahoo.com> wrote:
>
> > > DP wrote:
> > > > Our CMDB database (vendor supplied) stores information in a
> > > > singlevarchar2 field.
>
> > > > The DATA:
>
> > > > Name
> > > > -----------------------------------------------------------
> > > > MPORTWEB03(MPORTWEB03) : msdb
> > > > MPORTWEB03(MPORTWEB03) : pubs
> > > > MPORTWEB03(MPORTWEB03) : tempdb
> > > > pr04(droid) : DAFRANZ
> > > > pr04(droid) : DAMOLSO
> > > > pr04(droid) : DAQUINT
> > > > pr04(droid) : DBSNMP
>
> > > > What I can't figure out is how to select data up to or before the
> > > > first open paran.
>
> > > > I have tried RTIRM with no luck.
>
> > > Immediate solution:  use the built in SUBSTR and INSTR functions to
> > > extract the appropriate substrings. i.e. something like
> > >     select substr(name,0,instr(name,'(')-1) from table;
>
> > > Better solution: create view(s) and or procs using the functions above
> > > to allow you to query the table like a human being
>
> > > Best solution: ditch this abortion of a data design and replace it with
> > > something designed by someone with a clue.
>
> > > Good luck!
>
> > > //Walt- Hide quoted text -
>
> > > - Show quoted text -
>
> > Thanks!!!
>
> > I agree with you 100%. I would love to ditch this CMDB vendor, but
> > that is not my call..  I just have to make it work!  :-)
>
> > Dennis
>
> Propagate it to a better design on insert trigger?
>
> "I can't modify the database!"
>
> Extract it to a flat file with decent columns and declare it as an
> external table?
> Put it in your own schema and call that a different database? (cough,
> cough)
>
> jg
> --

yes copying the data to another table with decent structure would be my approach. Use their table as a staging table and convert it in PL/ SQL on the way to loading it in your table.

  ed Received on Thu Jan 03 2008 - 22:22:57 CST