Re: E-mail generator

[Steven Calvert <calvert@ULETH.CA>]

I wrote an email id generator a while ago using a stored Oracle PL/SQL
database procedure and do not specifically handle quotes, commas, spaces,
etc. People can and do put the strangest characters in name fields, especially
when you consider typos. So I just strip out ALL invalid characters using
a loop and substring / replace functions.The following PL/SQL code leaves
only lowercase letters, digits, periods, and underscores in the "name"
field:

  i := 1;
  while i <= length (name) loop
    ichr := substr (name, i, 1);
    if (ichr between 'a' and 'z') or
       (ichr between '0' and '9') or
       (ichr = '.') or (ichr = '_') then
      i := i + 1;
    else
      name := replace (name, ichr);
    end if;
  end loop;

It should be fairly easy to translate into SQR code using a while loop and substr / translate functions.

I am including the full PL/SQL procedure since you may want to include other things in your E-mail generator like

• a maximum email username length,
• a routine to create a unique email username (appends digits to the username until the username is not found in the database),
• a default value if the input username is null,
• a routine to remove "extra" periods, ...

Note that the following code gets somewhat complicated with the "seq" and
"change_num" fields. It does work, but really is only included to provide
some ideas. Please contact me privately if you would like more info:

  procedure p_unique_name (
    name                in out  varchar2,
    seq                 in      number,
    last_name           in      varchar2,
    first_name          in      varchar2
  ) is
  max_len number;
  id_found varchar2(1);
  zero_one_found varchar2(1);
  try_number varchar2(5);
  i number;
  j number;
  ichr varchar2(1);
  begin
  max_len := 21; /* Windows 95 only handles 21 character usernames */
  /* default username to firstname.lastname */
  if name is null then
    name := lower (nvl (replace (first_name, '.'), 'null') ||
            '.' || nvl (replace (last_name, '.'), 'null'));
  else
    name := lower (name);
  end if;
  /* allow only valid username characters */
  i := 1;
  while i <= length (name) loop
    ichr := substr (name, i, 1);
    if (ichr between 'a' and 'z') or
       (ichr between '0' and '9') or
       (ichr = '.') or (ichr = '_') then
      i := i + 1;
    else
      name := replace (name, ichr);
    end if;
  end loop;
  /* strip double periods out of the user name */
  i := 1;
  j := 0;
  while i <= length (name) loop
    ichr := substr (name, i, 1);
    if (ichr = '.') then
      if (j > 0) then
        name := substr (name, 1, j-1) || substr (name, j+1);
        i := i - 1;
      end if;
      j := i;
    end if;
    i := i + 1;
  end loop;
  /* make sure name does not begin or end with a period */
  if substr (name, 1, 1) = '.' then
    name := 'null' || name;
  end if;
  if substr (name, length (name), 1) = '.' then
    name := name || 'null';
  end if;
  id_found := 'Y';
  try_number := null;
  while id_found = 'Y' loop
    id_found := 'N';
    select max ('Y')
      into id_found
      from saturn.spyuser
     where spyuser_name =
           substr (name, 1, max_len - nvl(length(try_number),0)) || try_number
       and spyuser_change_num in (0, -1)
       and spyuser_seq <> seq;
    if id_found = 'Y' then
      zero_one_found := 'Y';
      while zero_one_found = 'Y' loop
        try_number := to_char (nvl (to_number (try_number), 1) + 1);
        if instr (try_number, '0') = 0 and instr (try_number, '1') = 0 then
          zero_one_found := 'N';
        end if;
      end loop;
    end if;
  end loop;
  name := substr (name, 1, max_len - nvl(length(try_number),0)) || try_number;
  end p_unique_name;

-----Original Message-----

From: Sam Spritzer [mailto:SSpritzer@GW.CTG.COM]
Sent: Thursday, April 22, 1999 3:55 PM
To: Multiple recipients of list SQR-USERS
Subject: E-mail generator

I m in the process of creating an e-mail generator which will take an
employee's name and create the user part of an e-mail address.  The
requirement is to take the first letter of the first name and marry it to
the last name.  For example, Bill Clinton would be bclinton.
Straight names are easy...its the non-letter characters I am having trouble
with such as O'Gore,Al / de Agnew, Spiro / Rodham-Clinton,Hillary.
Does anyone have a "stripper/scrubber" code that they would be willing to
share?
Thanks in advance,
Sam

--
Steven Calvert
calvert@uleth.ca
University of Lethbridge
(403)329-2071