Re: Query Help Needed

It works, thanks a lot!

GZ

Thomas Kyte wrote:

> In article <3BBA469F.793AB609_at_yahoo.com>, GZ says...
> >
> >I would like to know if there is a way to compare two similar but not
> >exactly the same strings in a where clause. I am using the INSTR
> >function to do this as below but it does not do a valid comparision.
> >Using ColumnA = ColumnB is not an option here nor is PL/SQL.
> >
> >The query below returns row one of the table but I do not want this row
> >returned.
> >
> >select * from My_Table where INSTR(ColumnA, ColumnB) = 0
> >
>
> that returns 0 rows given your example, you must mean "<> 0" right..
>
> >The row is returned because it finds that Dept1 is in the string of
> >ColumnA. Is there a way so that the correct comparison of
> >Dept1--->Dept1C is performed and that row is therefore not returned?
> >
>
> Ok, it sounds like columnA is a delimited list of "names". You want to find
> rows such that columnB is one of the names in the list of columnA. Since
> columnA seems to be a comma delimited list, this will work:
>
> select * from my_table
> where instr( ','||columnA||',', ','||columnB||',') <> 0;
>
> that'll make sure columb is in the "list"
>
> >
> >My_Table
> >-----------
> >
> >ColumnA ColumnB
> >----------- ----------
> >
> >Dept1C,Dept99 Dept1
> >
> >
> >Any help would be much appreciated.
> >
> >Thanks,
> >
> >Greg
> >
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Wed Oct 03 2001 - 19:03:50 CDT