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

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sun, 4 Oct 1998 15:14:58 +0200
Message-ID: <6v7sck$129$1@pascal.a2000.nl>

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

create or replace function sameChars ( pStr1 in varchar2

                                     , pStr2 in varchar2
                                     ) return varchar2

is
begin

• each character in pStr1 that is also found in pStr2 is replaced
• by itself. All others are removed. Not that in PL/SQL 2.2 duplicates
• in s2 or s3 in calls to translate(s1, s2, s3) result in a null return
• value. Therefore, usedChars is called. In newer version of PL/SQL,
• usedChars is only needed in the first argument. -- return translate( usedChars( pStr1 ) , usedChars( pStr2 || pStr1 ) , usedChars( pStr2 ) ); -- end sameChars;
  /
  

create or replace function diffChars ( pStr1 in varchar2

                                     , pStr2 in varchar2
                                     ) return varchar2

is
begin

• Each character in pStr1 that is also found in pStr2 is deleted. However,
• the third argument in translate(..) may not be null. Therefore, a tab
• is prefixed prior to calling usedChars(..). Note that usedChars will not
• sort the characters, so the tab will always be the first character. It
• will be removed by substr(..). For this reason, tabs are not taken into
• account when comparing the strings. -- return substr( translate( usedChars( chr(8) || pStr1 ) , usedChars( chr(8) || pStr2 ) , chr(8) ) , 2 ) || substr( translate( usedChars( chr(8) || pStr2 ) , usedChars( chr(8) || pStr1 ) , chr(8) ) , 2 ); end diffChars;
  /
  

create or replace function usedAlphas( pStr in varchar2 ) return varchar2
as
begin
  return sameChars( pStr

                  ,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
                  );

end usedAlphas;
/

create or replace function usedNonAlphas( pStr in varchar2 ) return varchar2
as
begin
  return diffChars( pStr, usedAlphas( pStr ) ); end usedNonAlphas;
/

create or replace function usedNumerics( pStr in varchar2 ) return varchar2
as
begin
  return sameChars( pStr, '01234567890' ); end usedNumerics;
/

create or replace function usedNonNumerics( pStr in varchar2 ) return varchar2
as
begin
  return diffChars( pStr, usedNumerics( pStr ) ); end usedNonNumerics;
/

create or replace function usedAlphanumerics( pStr in varchar2 )   return varchar2
as
begin
  return sameChars

           ( pStr
           ,
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
           );

end usedAlphanumerics;
/

create or replace function usedNonAlphanumerics( pStr in varchar2 )   return varchar2
as
begin
  return diffChars( pStr, usedAlphanumerics( pStr ) ); end usedNonAlphanumerics;
/

create or replace function countUsedChars( pStr in varchar2 ) return number
is
begin
  return nvl( length( usedChars( pStr ) ), 0 ); end countUsedChars;
/

create or replace function countUsedAlphas( pStr in varchar2 ) return number
is
begin
  return nvl( length( usedAlphas( pStr ) ), 0 ); end countUsedAlphas;
/

create or replace function countUsedNonAlphas( pStr in varchar2 ) return number
is
begin
  return nvl( length( usedNonAlphas( pStr ) ), 0 ); end countUsedNonAlphas;
/

create or replace function countUsedNumerics( pStr in varchar2 ) return number
is
begin
  return nvl( length( usedNumerics( pStr ) ), 0 ); end countUsedNumerics;
/

create or replace function countUsedNonNumerics( pStr in varchar2 )   return number
is
begin
  return nvl( length( usedNonNumerics( pStr ) ), 0 ); end countUsedNonNumerics;
/

create or replace function countUsedAlphanumerics( pStr in varchar2 ) return number
is
begin
  return nvl( length( usedAlphanumerics( pStr ) ), 0 ); end countUsedAlphanumerics;
/

create or replace function countUsedNonAlphanumerics( pStr in varchar2 )
return number
is
begin
  return nvl( length( usedNonAlphanumerics( pStr ) ), 0 ); end countUsedNonAlphanumerics;
/
Received on Sun Oct 04 1998 - 08:14:58 CDT