Re: Distinct string statement

Marina S. (marina.sukhnev_at_thinknet.com) wrote:

: id symbol

: 11 abc,bbc,abf,cde
: 22 rda,abc,aig
: 33 ibm,aig,sun
: 55 aig,klm,suk,sun
: 23 bbc,kln,sun,adf

: I need to select distinct symbol strings from symbol column,they
: shoudn't be repeated,but should be comma separated as they appear in a
: column.
: the result to user should be like:
: abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...

Paul Izzo wrote:
[...]

> 
>   In order to do your create your UNION statement you need to know what
> your up against.  Do a couple queries in order to:
> 

To do this right is going to require some PL/SQL, since there doesn't seem to be any restriction as to how many comma-separated strings can be in one row of "symbol", or how long the strings are.

The functions I referred to earlier (easily located via Google) have a piece of code that uses the instr() and substr() built-in functions to loop through each string looking for the next delimiter (comma) and peel off the delimited value until you run out of delimiters. The only other tricky part is to return the strings in the order encounterd, not in sorted order, so a DISTINCT is not going to do the job.

Whether this is homework or not, that is about the only sure way to do this.

-Mark Bole Received on Tue Feb 22 2005 - 12:25:56 CST