CREATE OR REPLACE FUNCTION check_alpha(p_string VARCHAR2) RETURN INTEGER AS
--=============================================================================
-- Copywright 2006, chet west
--=============================================================================
-- DESCRIPTION
--	- simple function to check a string to  see if it has non-alpha 
--      for validating a person's name chaaracters.
--      - it does allow for <space>, <quotte> and <hyphen>
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- chet        01/30/2006 Initial Creationn - Based on function origninally 
--                        written by Stevee A. Taylor
--=============================================================================
   len INTEGER := 0;
   i INTEGER;
   retval INTEGER := 1;
   asciivar INTEGER;
   charvar  VARCHAR(1);
BEGIN

   SELECT LENGTH(p_string)
     INTO len
     FROM SYS.DUAL;

   i := 1;

   WHILE(i < len + 1) LOOP
      SELECT ASCII(SUBSTR(p_string, i, i))
--            ,SUBSTR(p_string,i,1)
        INTO asciivar
--            ,charvar
        FROM SYS.DUAL;

      IF  ((asciivar >= 65) AND (asciivar <= 90))
      OR  ((asciivar >= 97) AND (asciivar <= 122))
      OR  ((asciivar = 32) AND ((i > 1) AND (i < len)))
      OR  ((asciivar = 39) AND ((i > 1) AND (i = 2)))
-- comma      OR  ((asciivar = 44))
      OR  ((asciivar = 45) AND ((i > 1) AND (i < len)))
-- period     OR  ((asciivar = 46))
      THEN
         retval := 0;
      ELSE
         retval := 1;
         exit;
      END IF;

      i := i + 1;
   END LOOP;

   RETURN retval;
END check_alpha;
/