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

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

--
@home.com is bogus.
http://slashdot.org/~twitter/journal/