合并字段

 

Hi, I would like to remove the duplicates from the string seperated by coma using the pl/sql functions. How can I achieve this? Thanks

Qwerty Posts: 876 Registered: 12/03/08

Re: Removing duplicates from a string Posted: Aug 7, 2009 7:24 AM   in response to: user10860137     Reply please give some sample input and required output. your DB version.

SanjayRs Posts: 3,039 Registered: 01/08/06

Re: Removing duplicates from a string Posted: Aug 7, 2009 7:28 AM   in response to: user10860137 Correct   Reply You don't need plsql with t as (select 'SCOTT,ALLEN,KING,SCOTT' as in_cls from dual) , t1 as ( select distinct regexp_substr(in_cls, '[^,]+' , 1, rownum) names from t connect by rownum <= length(regexp_replace(in_cls, '[^,]' ))+1) SELECT RTrim(xmlagg(xmlelement(a,names||',' ).extract('//text()' )),',' ) string from t1 /    STRING ------------------------------------------------ ALLEN,SCOTT,KING   1 row selected. SS

Qwerty Posts: 876 Registered: 12/03/08

Re: Removing duplicates from a string Posted: Aug 7, 2009 7:56 AM   in response to: user10860137 Helpful   Reply if your version in below 10g then you can try something like this SQL> with t as(select 'SCOTT,ALLEN,KING,SCOTT' test_string from dual) 2 select distinct substr(test_string 3 ,decode(level,1,1,instr(test_string,',' ,1,level-1)+1) 4 ,decode(level,4,length(test_string),instr(test_string,',' ,1,level)-decode(level,1,0,instr(test_string,',' ,1,level-1))-1)) from t 5 connect by level<=length(test_string)-length(replace(test_string,',' ))+1;   SUBSTR(TEST_STRING,DECODE(LEVEL,1,1,INSTR(TEST_STRING,',' ,1,LEVEL-1)+1),DECODE(LEVEL,4,L ---------------------------------------------------------------------------------------- ALLEN SCOTT KING   3 rows selected.

user10860137 Posts: 104 Registered: 01/26/09

Re: Removing duplicates from a string Posted: Aug 7, 2009 7:58 AM   in response to: SanjayRs     Reply Hi Sanjay, Thanks it worked

Sven W. Posts: 4,778 Registered: 10/13/00

Re: Removing duplicates from a string Posted: Aug 7, 2009 8:00 AM   in response to: user10860137     Reply I think michaels2 will come up with a nice looking XML solution....

SanjayRs Posts: 3,039 Registered: 01/08/06

Re: Removing duplicates from a string Posted: Aug 7, 2009 8:00 AM   in response to: user10860137     Reply You are welcome!

Walter Fernández Posts: 1,544 Registered: 08/16/08

Re: Removing duplicates from a string Posted: Aug 7, 2009 8:16 AM   in response to: user10860137     Reply Hi, CREATE OR REPLACE PACKAGE BODY pack_test IS   FUNCTION get_array_from_string(p_ds_parameter IN VARCHAR2, p_cd_separator IN VARCHAR2) RETURN T_ARRAY IS v_ds_parameter VARCHAR2(4000); v_nr_begin NUMBER(9) := 1; v_nr_position NUMBER(9) := 1; v_arr_string T_ARRAY; BEGIN v_ds_parameter := p_ds_parameter || p_cd_separator; LOOP v_nr_position := INSTR(v_ds_parameter, p_cd_separator, v_nr_begin, 1); EXIT WHEN(NVL(v_nr_position, 0) = 0); v_arr_string(SUBSTR(v_ds_parameter, v_nr_begin, v_nr_position - v_nr_begin)) := 1; v_nr_begin := v_nr_position + 1; END LOOP; RETURN v_arr_string; END get_array_from_string;   PROCEDURE proc_filter_string(p_string IN VARCHAR2) IS v_array t_array; counter VARCHAR2(30); BEGIN v_array := get_array_from_string(p_ds_parameter => p_string, p_cd_separator => ',' ); counter := v_array.FIRST; -- This loop print elements without repeat WHILE counter IS NOT NULL LOOP DBMS_OUTPUT.PUT_LINE(counter); counter := v_array.NEXT(counter); END LOOP; END; END pack_test; Regards,

SanjayRs Posts: 3,039 Registered: 01/08/06

Re: Removing duplicates from a string Posted: Aug 7, 2009 8:17 AM   in response to: Qwerty     Reply oracle_for_dude wrote: if your version in below 10g then you can try something like this SQL> with t as(select 'SCOTT,ALLEN,KING,SCOTT' test_string from dual) 2 select distinct substr(test_string 3 ,decode(level,1,1,instr(test_string,',' ,1,level-1)+1) 4 ,decode(level,4,length(test_string),instr(test_string,',' ,1,level)-decode(level,1,0,instr(test_string,',' ,1,level-1))-1)) from t 5 connect by level<=length(test_string)-length(replace(test_string,',' ))+1; SUBSTR(TEST_STRING,DECODE(LEVEL,1,1,INSTR(TEST_STRING,',' ,1,LEVEL-1)+1),DECODE(LEVEL,4,L ---------------------------------------------------------------------------------------- ALLEN SCOTT KING 3 rows selected. Hi, XMLAGG work in 9i too, you have to only substitute the regexp part in my solution to make it work on 9i with t as(select 'SCOTT,ALLEN,KING,SCOTT' test_string from dual) select distinct substr(test_string ,decode(level,1,1,instr(test_string,',' ,1,level-1)+1) ,decode(level,4,length(test_string),instr(test_string,',' ,1,level)-decode(level,1,0,instr(test_string,',' ,1,level-1))-1)) from t connect by level<=length(test_string)-length(replace(test_string,',' ))+1 /    SUBSTR(TEST_STRING,DEC ---------------------- ALLEN   1 row selected.   SQL> select * from V$version ;   BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for 32-bit Windows: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production Here is the fix with t as(select 'SCOTT,ALLEN,KING,SCOTT' test_string from dual) , t1 as ( select distinct substr(test_string ,decode(level,1,1,instr(test_string,',' ,1,level-1)+1) ,decode(level,4,length(test_string),instr(test_string,',' ,1,level)-decode(level,1,0,instr(test_string,',' ,1,level-1))-1)) from t connect by level<=length(test_string)-length(replace(test_string,',' ))+1 ) select * from t1 /    SUBSTR(TEST_STRING,DEC ---------------------- ALLEN KING SCOTT   3 rows selected. SS

Qwerty Posts: 876 Registered: 12/03/08

Re: Removing duplicates from a string Posted: Aug 7, 2009 8:22 AM   in response to: SanjayRs     Reply Hi The solution which I gave works only when there is a string with three comma seperated value here is the complete solution SQL> with t as(select 'SCOTT,ALLEN,KING,SCOTT,JONES,KING' test_string from dual) 2 select distinct substr(test_string 3 ,decode(level,1,1,instr(test_string,',' ,1,level-1)+1) 4 ,decode(level,length(test_string)-length(replace(test_string,',' ))+1,length(test_string),instr(test_string,',' ,1,level)-decode(level,1,0,instr(test_string,',' ,1,level-1))-1)) from t 5 connect by level<=length(test_string)-length(replace(test_string,',' ))+1;   SUBSTR(TEST_STRING,DECODE(LEVEL,1,1,INSTR(TEST_STRING,',' ,1,LEVEL-1)+1),DECODE(LEVEL,LENGTH(TEST_STRING)-LENGTH(REPLACE(TEST_STRING, ------------------------------------------------------------------------------------------------------------------------------------ ALLEN JONES SCOTT KING   4 rows selected. though its very complicated.

SanjayRs Posts: 3,039 Registered: 01/08/06

Re: Removing duplicates from a string Posted: Aug 7, 2009 8:40 AM   in response to: Qwerty     Reply Here it is what I was trying to point out. SQL> select * from v$version ;   BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production PL/SQL Release 9.2.0.8.0 - Production CORE 9.2.0.8.0 Production TNS for 32-bit Windows: Version 9.2.0.8.0 - Production NLSRTL Version 9.2.0.8.0 - Production   5 rows selected.   SQL> select level from dual connect by level <5;   LEVEL ---------- 1   1 row selected.   /* THE SOLUTION */ SQL> select * from ( select level from dual connect by level < 5 ) ;   LEVEL ---------- 1 2 3 4   4 rows selected.     SQL> connect scott/tiger@Oracl10G Connected.   SQL> select * from v$version ;   BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production   5 rows selected.   SQL> select level from dual connect by level <5;   LEVEL ---------- 1 2 3 4   4 rows selected. SS

michaels2 Posts: 6,782 Registered: 09/24/06

Re: Removing duplicates from a string Posted: Aug 7, 2009 10:44 AM   in response to: Sven W.     Reply I think michaels2 will come up with a nice looking XML solution.... Don't know if it is nice looking, but in any case it is fun A 11g solution only: SQL> with t as ( select 'SCOTT,ALLEN,KING,SCOTT' as in_cls from dual ) -- -- select trim(column_value) in_cls from t, xmltable(('string-join(distinct-values(("' ||replace(in_cls,',' ,'","' )||'")),",")' )) /  IN_CLS -------------------- ALLEN,KING,SCOTT 1 row selected.