Recursive PL/SQL
it will be a good week !
I found an elegant way to solve a query with recursive pl/sql.
an user wanted to have DHSGHDADSFDF translated in DHSGAF, that is, duplicated chars removed, order retained.
here is my function :
create or replace function f(v varchar2) return varchar2 is
begin
if (v is null) then return null;
else return substr(v,1,1)||f(replace(substr(v,2),substr(v,1,1));
end if;
end;
/
SQL> select f(‘DHSGHDADSFDF’) from dual
2 /
F(‘DHSGHDADSFDF’)
—————————————-
DHSGHDADSFDF
SQL>
?
Probably is meant
CREATE OR REPLACE FUNCTION f(v VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF (v IS NULL) THEN
RETURN NULL;
ELSE
RETURN Substr(v, 1, 1) || f(REPLACE(v, Substr(v, 1, 1), ”));
END IF;
END;
/
Regards
Maxim
sorry friends, I apologize for this copy paste error… I just posted my first try, but now I corrected my post!
Laurent,
Your recursive pl/sql solution is very elegant !
I like it !!
Here are two alternative pure sql solutions :
http://oraqa.com/2007/04/01/how-to-remove-duplicate-chararacters-from-a-string-in-a-sql-statement-original-order-retained/
Thanks,
Frank