Set consolidation: Difference between revisions
Content added Content deleted
m (→{{header|Phix}}: syntax coloured) |
|||
Line 2,416: | Line 2,416: | ||
(H I K) (A B) (C D) (D B) (F G H) |
(H I K) (A B) (C D) (D B) (F G H) |
||
==> (A C D B) (I K F G H) |
==> (A C D B) (I K F G H) |
||
</pre> |
|||
=={{header|SQL}}== |
|||
{{works with|ORACLE 19c}} |
|||
This is not a particularly efficient solution, but it gets the job done. |
|||
<lang SQL> |
|||
/* |
|||
This code is an implementation of "Set consolidation" in SQL ORACLE 19c |
|||
p_list_of_sets -- input string |
|||
delimeter by default "|" |
|||
*/ |
|||
with |
|||
function set_consolidation(p_list_of_sets in varchar2) |
|||
return varchar2 is |
|||
-- |
|||
v_list_of_sets varchar2(32767) := p_list_of_sets; |
|||
v_output varchar2(32767) ; |
|||
v_set_1 varchar2(2000) ; |
|||
v_set_2 varchar2(2000) ; |
|||
v_pos_set_1 pls_integer; |
|||
v_pos_set_2 pls_integer; |
|||
-- |
|||
function remove_duplicates(p_set varchar2) |
|||
return varchar2 is |
|||
v_set varchar2(1000) := p_set; |
|||
begin |
|||
for i in 1..length(v_set) |
|||
loop |
|||
v_set := regexp_replace(v_set, substr(v_set, i, 1), '', i+1, 0) ; |
|||
end loop; |
|||
return v_set; |
|||
end; |
|||
-- |
|||
begin |
|||
--cleaning |
|||
v_list_of_sets := ltrim(v_list_of_sets, '{') ; |
|||
v_list_of_sets := rtrim(v_list_of_sets, '}') ; |
|||
v_list_of_sets := replace(v_list_of_sets, ' ', '') ; |
|||
v_list_of_sets := replace(v_list_of_sets, ',', '') ; |
|||
--set delimeter "|" |
|||
v_list_of_sets := replace(v_list_of_sets, '}{', '|') ; |
|||
-- |
|||
<<loop_through_sets>> |
|||
while regexp_count(v_list_of_sets, '[^|]+') > 0 |
|||
loop |
|||
v_set_1 := regexp_substr(v_list_of_sets, '[^|]+', 1, 1) ; |
|||
v_pos_set_1 := regexp_instr(v_list_of_sets, '[^|]+', 1, 1) ; |
|||
-- |
|||
<<loop_for>> |
|||
for i in 1..regexp_count(v_list_of_sets, '[^|]+')-1 |
|||
loop |
|||
-- |
|||
v_set_2 := regexp_substr(v_list_of_sets, '[^|]+', 1, i+1) ; |
|||
v_pos_set_2 := regexp_instr(v_list_of_sets, '[^|]+', 1, i+1) ; |
|||
-- |
|||
if regexp_count(v_set_2, '['||v_set_1||']') > 0 then |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_1, remove_duplicates(v_set_1||v_set_2), v_pos_set_1, 1) ; |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_2, '', v_pos_set_2, 1) ; |
|||
continue loop_through_sets; |
|||
end if; |
|||
-- |
|||
end loop loop_for; |
|||
-- |
|||
v_output := v_output||'{'||rtrim(regexp_replace(v_set_1, '([A-Z])', '\1,'), ',') ||'}'; |
|||
v_list_of_sets := regexp_replace(v_list_of_sets, v_set_1, '', 1, 1) ; |
|||
-- |
|||
end loop loop_through_sets; |
|||
-- |
|||
return replace(nvl(v_output,'{}'),'}{','},{') ; |
|||
end; |
|||
--Test |
|||
select lpad('{}',50) || ' ==> ' || set_consolidation('{}') as output from dual |
|||
union all |
|||
select lpad('{},{}',50) || ' ==> ' || set_consolidation('{},{}') as output from dual |
|||
union all |
|||
select lpad('{},{B}',50) || ' ==> ' || set_consolidation('{},{B}') as output from dual |
|||
union all |
|||
select lpad('{D}',50) || ' ==> ' || set_consolidation('{D}') as output from dual |
|||
union all |
|||
select lpad('{F},{A},{A}',50) || ' ==> ' || set_consolidation('{F},{A},{A}') as output from dual |
|||
union all |
|||
select lpad('{A,B},{B}',50) || ' ==> ' || set_consolidation('{A,B},{B}') as output from dual |
|||
union all |
|||
select lpad('{A,D},{D,A}',50) || ' ==> ' || set_consolidation('{A,D},{D,A}') as output from dual |
|||
union all |
|||
--Test RosettaCode |
|||
select '-- Test RosettaCode' as output from dual |
|||
union all |
|||
select lpad('{A,B},{C,D}',50) || ' ==> ' || set_consolidation('{A,B},{C,D}') as output from dual |
|||
union all |
|||
select lpad('{A,B},{B,D}',50) || ' ==> ' || set_consolidation('{A,B},{B,D}') as output from dual |
|||
union all |
|||
select lpad('{A,B},{C,D},{D,B}',50) || ' ==> ' || set_consolidation('{A,B},{C,D},{D,B}') as output from dual |
|||
union all |
|||
select lpad('{H, I, K}, {A,B}, {C,D}, {D,B}, {F,G,H}',50) || ' ==> ' || set_consolidation('{H, I, K}, {A,B}, {C,D}, {D,B}, {F,G,H}') as output from dual |
|||
union all |
|||
select lpad('HIK|AB|CD|DB|FGH',50) || ' ==> ' || set_consolidation('HIK|AB|CD|DB|FGH') as output from dual |
|||
; |
|||
/ |
|||
</lang> |
|||
{{out}} |
|||
<pre> |
|||
{} ==> {} |
|||
{},{} ==> {} |
|||
{},{B} ==> {B} |
|||
{D} ==> {D} |
|||
{F},{A},{A} ==> {F},{A} |
|||
{A,B},{B} ==> {A,B} |
|||
{A,D},{D,A} ==> {A,D} |
|||
-- Test RosettaCode |
|||
{A,B},{C,D} ==> {A,B},{C,D} |
|||
{A,B},{B,D} ==> {A,B,D} |
|||
{A,B},{C,D},{D,B} ==> {A,B,D,C} |
|||
{H, I, K}, {A,B}, {C,D}, {D,B}, {F,G,H} ==> {H,I,K,F,G},{A,B,D,C} |
|||
HIK|AB|CD|DB|FGH ==> {H,I,K,F,G},{A,B,D,C} |
|||
</pre> |
</pre> |
||