Research in a list of varchar... 1

[hpaille]

Hi,

I have a list of value (strings, with a comma between the words to separate them), and for each words of this list I would like to know if it is in another list (built in the same way). Is there a simple way to do that in Pl sql ?

list1:='NUM_FOURN,NUM_PDV,COD_VAT'
list2:='NUM_PDV,COD_VAT'
I want to know which elements of list1 is in list2

Hugues

 

[SantaMufasa]

HPaille,

Perhaps this is what you want:

Section 1 -- Invocation of PL/SQL function using your test values:

var list1 varchar2(50)
var list2 varchar2(50)
exec :list1 := 'COD_VAT,NUM_FOURN, NUM_PDV'
exec :list2 := 'NUM_PDV,COD_VAT'
col a heading "List 1s in List 2" format a20
select compare_lists(:list1,:list2) a from dual;

List 1s in List 2
------------------
COD_VAT,NUM_PDV

1 row selected.

Section 2 -- PL/SQL code for "compare_lists" function:

create or replace function compare_lists (x varchar2, y varchar2) return varchar2 is
	type varchar_stencil is table of varchar2(2000) index by binary_integer;
	x_tab varchar_stencil;
	y_tab varchar_stencil;
	x_slots		number;
	y_slots		number;
	idx		binary_integer;
	same_values	varchar2(4000);
	separator	varchar2(10);
	procedure parse (str varchar2, which_tab out varchar_stencil) is
		hold_str	varchar2(2000);
		pos		number;
	begin
		hold_str	:= trim(str);
		pos	:= instr(hold_str,',');
		if pos = 0 then
			which_tab(1)	:= trim(str);
		else
			idx	:= 0;
			while pos > 0 loop
				idx	:= idx+1;
				which_tab(idx)	:= substr(hold_str,1,pos-1);
				hold_str	:= substr(hold_str,pos+1);
				pos	:= instr(hold_str,',');
			end loop;
			if length(trim(hold_str))>0 then
				idx	:= idx+1;
				which_tab(idx)	:= trim(hold_str);
			end if;
		end if;
	end;
	procedure compare is
	begin
		separator	:= null;
		for i in 1..x_slots loop
			for n in 1..y_slots loop
				if upper(x_tab(i)) = upper(y_tab(n)) then
					same_values	:= same_values||separator||x_tab(i);
					separator	:= ',';
				end if;
			end loop;
		end loop;
	end;
begin
	parse(x,x_tab);
	x_slots	:= idx;
	parse(y,y_tab);
	y_slots	:= idx;
	compare;
	return same_values;
end;
/

Function created.

Let us know is this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:40 (17Aug04) UTC (aka "GMT" and "Zulu"), 18:40 (16Aug04) Mountain Time)

 

[hpaille]

Thank you, this what I eventually did (or almost !)