Re: Query Help Needed

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