Laurent Schneider

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;
/

ref: using recursion with PL/SQL

This entry was posted on Monday, January 30th, 2006 at 15:23 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

4 Responses to “Recursive PL/SQL”

Francois Degrelle Says:
January 30th, 2006 at 16:13
SQL> select f(’DHSGHDADSFDF’) from dual
2 /

F(’DHSGHDADSFDF’)
—————————————-
DHSGHDADSFDF

SQL>

?
Anonymous Says:
January 30th, 2006 at 18:36
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
Laurent Schneider Says:
January 30th, 2006 at 22:51
sorry friends, I apologize for this copy paste error… I just posted my first try, but now I corrected my post!
Frank Zhou Says:
April 2nd, 2007 at 15:09
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

Use <code> and </code> to post code

Recursive PL/SQL

4 Responses to “Recursive PL/SQL”

Leave a Reply