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

On Jan 3, 2:38 pm, DP <dxpe..._at_gmail.com> 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
>
> First SQL
>
> I can select  the data after the : with-
> select substr(name,instr(name,': ')+2,length(name)) from table;
>
> msdb
> pubs
> tempdb
> DAFRANZ
> DAMOLSO
> DAQUINT
> 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.
>
> Any Help you can give me would be greatly appreciated.
> Thanks,
>
> Dennis

Hello Dennis,

Is this what you're looking for?

SQL> variable v_string varchar2(1024);
SQL> exec :v_string := '&1';
Enter value for 1: pr04(droid):DAFRANZ

PL/SQL procedure successfully completed.

SQL> select substr(:v_string,1,instr(:v_string,'(')-1) from dual;

SUBSTR(:V_STRING,
1,INSTR(:V_STRING,'(')-1)

Regards,
Arun Received on Thu Jan 03 2008 - 13:51:12 CST