Re: distinct and replace...can anyone help?

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 13 Sep 2001 16:41:09 +0200
Message-ID: <9nqgi6$9g7$1@s1.read.news.oleane.net>

"kinch" <kinch55_at_yahoo.com> a écrit dans le message news: 4566a8e8.0109130542.77eaa46e_at_posting.google.com...
> Hi,
>
> I have a legacy database to deal with where the company name field is
> of the form -
> Company Name (Branch Name).
>
> Companies with no branches are in the form -
> Company Name
>
> I want to select just the company name and i can do this with
> select replace(name,substr(name,instr(name, ' ('))) from table;
>
> This works fine and gets me a list of company names only.
>
> I need to wrap a distinct round this though and when i do I do get
> just one version of any of the companies with multiple
> branches...unfortunately when i use the distinct I don`t get any of
> the companies with no branches.
>
> Anyone know why this is?
>
> Cheers,
> kinch

v817>create table t (comp varchar2(100));
v817>insert into t values ('IBM (RD)');
v817>insert into t values ('IBM (HQ)');
v817>insert into t values ('Oracle (RD)');
v817>insert into t values ('Oracle (HQ)');
v817>insert into t values ('FORD');
v817>insert into t values ('GENERAL MOTORS');
v817>commit;
v817>select * from t;

COMP

IBM (RD)
IBM (HQ)
Oracle (RD)
Oracle (HQ)
FORD
GENERAL MOTORS 6 rows selected.

v817>select distinct substr (comp, 1,

  2                                decode (instr(comp, ' ('),
  3                                        0, length(comp),
  4                                        instr(comp, ' (')))

5 from t;

SUBSTR(COMP,1,DECODE(INSTR(COMP,'('),0,LENGTH(COMP),INSTR(COMP,'(')))

FORD
GENERAL MOTORS
IBM
Oracle

4 rows selected.

--
Regards
Michel

Received on Thu Sep 13 2001 - 09:41:09 CDT