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

From: Walt <>
Date: Thu, 03 Jan 2008 15:04:50 -0500
Message-ID: <>

DP wrote:
> Our CMDB database (vendor supplied) stores information in a
> singlevarchar2 field.
> The DATA:
> Name
> -----------------------------------------------------------
> 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

Original text of this message