Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve unique values from a string (oracle 11g)
How to retrieve unique values from a string [message #576510] Tue, 05 February 2013 14:49 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Objective: I need to compile a final string by concatinating the unique values from different strings.

Here is the script to create tables and data.

Create table temp_acronyms(id number, acronym varchar2(30);

insert into temp_acronyms values(1, 'ABC');

insert into temp_acronyms values(2, 'DEC//NOFO');

insert into temp_acronyms values(3, 'CBK//FO TO USA');

insert into temp_acronyms values(4, 'DEC//NO ENTRY');

insert into temp_acronyms values(5, 'ABC//NOFO');

COMMIT;

select * from temp_acronyms;

ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO

I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.

my final string should have the values as below

ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

your prompt reply is greatly appreciated.

Thanks
Re: How to retrieve unique values from a string [message #576512 is a reply to message #576510] Tue, 05 February 2013 15:05 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select replace(sys_connect_by_path(acronym,'#'),'#') res 
  2  from temp_acronyms 
  3  where connect_by_isleaf=1
  4  connect by prior id = id-1
  5  start with id=1
  6  /
RES
------------------------------------------------------------------------
ABCDEC//NOFOCBK//FO TO USADEC//NO ENTRYABC//NOFO

Regards
Michel
Re: How to retrieve unique values from a string [message #576514 is a reply to message #576512] Tue, 05 February 2013 15:21 Go to previous messageGo to next message
Littlefoot
Messages: 19630
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel, your output doesn't seem to be correct (according to OP's request).

Here's another option:
SQL> select * from temp_acronyms;

        ID ACRONYM
---------- ------------------------------
         1 ABC
         2 DEC//NOFO
         3 CBK//FO TO USA
         4 DEC//NO ENTRY
         5 ABC//NOFO

SQL> with temp as
  2    (select distinct regexp_substr (acronym, '[^//]+', 1, level) col
  3     from temp_acronyms
  4     connect by regexp_substr (acronym, '[^//]+', 1, level) is not null
  5    )
  6  select rtrim(xmlagg(xmlelement (e, col || '//') order by col).extract ('//text()'), '//') result
  7  from temp;

RESULT
--------------------------------------------------------------------------------
ABC//CBK//DEC//FO TO USA//NO ENTRY//NOFO

SQL>
Re: How to retrieve unique values from a string [message #576515 is a reply to message #576512] Tue, 05 February 2013 15:22 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Michael,

Thanks for your quick response

by my final output should be as I mentioned i.e ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

after each unique acroynum it should have '//' after the string.

Thanks
Re: How to retrieve unique values from a string [message #576517 is a reply to message #576515] Tue, 05 February 2013 15:35 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just slightly modify the replace:
SQL> select ltrim(replace(sys_connect_by_path(acronym,'#'),'#','//'),'/') res
  2  from temp_acronyms 
  3  where connect_by_isleaf=1
  4  connect by prior id = id-1
  5  start with id=1
  6  /
RES
------------------------------------------------------------------------------
ABC//DEC//NOFO//CBK//FO TO USA//DEC//NO ENTRY//ABC//NOFO

@Littlefoot, OP's result is inconsistent with specifications, I chose to follow the specifications: Wink

Quote:
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable.


And for me all values in acronyms are distinct.

Regards
Michel

[Updated on: Tue, 05 February 2013 15:35]

Report message to a moderator

Re: How to retrieve unique values from a string [message #576518 is a reply to message #576515] Tue, 05 February 2013 15:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@gentleman777us

Note that storing multiple values in a field is a VERY BAD design.
Read Normalization.
The one that did that should be fired.

Regards
Michel
Re: How to retrieve unique values from a string [message #576519 is a reply to message #576514] Tue, 05 February 2013 15:37 Go to previous message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Thanks! a lot littlefoot.
Previous Topic: create trigger to create view but give me error
Next Topic: Need help in writing a code for when condition
Goto Forum:
  


Current Time: Sat Sep 20 13:25:19 CDT 2014

Total time taken to generate the page: 0.10621 seconds