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

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 Received on Thu Jan 03 2008 - 14:04:50 CST