Re: Here is how to transform "DADD2PBA" to "DA2PPB"

On Sun, 4 Oct 1998 15:14:58 +0200, "Arjan van Bentem" <avbentem_at_DONT-YOU-DAREdds.nl> wrote:

>Arjan van Bentem wrote
>>I'd like to know how many different characters are in a string,
>
>Meanwhile, I have come up with the following functions -- the names
>are not perfect yet. Ideas and comments welcome!
>
>Arjan.
>
>create or replace function usedChars( pStr in varchar2 ) return
>varchar2
>as
> vResult varchar2(2000);
> vChar varchar2(1);
>begin
> -- usedChars does not sort the found characters. For example:
>DDAPaHP returns
> -- DAPaH. Note that the function diffChars relies on this behavior!
> --
> for i in 1..length( pStr )
> loop
> vChar := substr( pStr, i, 1 );
> -- note that instr( null, .. ) yields null, not zero
> if nvl( instr( vResult, vChar ), 0 ) = 0
> then
> vResult := vResult || vChar;
> end if;
> end loop;
> return vResult;
>end usedChars;
>/

This is the thing that makes me cringe most about PL/SQL: the need to declare temporary strings with a maximum size: in this case 2000 characters. I know this seems large enough for all practical purposes (and probably is), but Oracle 8 allows the limit to go up to 4000 characters, which could result in this code producing a VALUE_ERROR exception, which is always embarrasing.

Here's a version which doesn't require that maximum limit: create or replace

FUNCTION remdups(a in varchar2)
RETURN varchar2 IS
BEGIN
  IF a IS NULL THEN RETURN NULL;
  ELSE RETURN SUBSTR(a, 1, 1)||

       remdups(REPLACE(SUBSTR(a, 2), SUBSTR(a, 1, 1)));   END IF;
END; The first character of a string is returned. We remove this character from the remainder of the string, and repeat the duplication removal on that remainder. Doncha just lurv recursion. :-)

--
Tommy Wareing
MIS Group
Learning Resources
Oxford Brookes University
01865 483389 Received on Wed Oct 28 1998 - 06:55:22 CST