---          Custom Sample SQL for Perl/PostgreSQL version 0.1

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    http://www.gnu.org/copyleft/gpl.html.

drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front. 
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end. 
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space. 
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up. 
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up. 
select clean_alpha ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just to show you what this function cleans up.
select clean_numeric ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (1111);