Re: Replacing control chars

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Fri, 03 Jan 2003 10:04:15 -0800
Message-ID: <F001.00526B57.20030103100415@fatcity.com>

>From AskTom

ops$tkyte_at_8i> create or replace package body utils   2 as
  3
  4 g_bad_chars varchar2(256);
  5 g_a_bad_char varchar2(256);
  6
  7 function strip_bad( p_string in varchar2 ) return varchar2
  8 is
  9 begin

 10      return replace(
 11                 translate( p_string,
 12                            g_bad_chars,
 13                            g_a_bad_char ),
 14                 substr( g_a_bad_char, 1, 1 ),
 15                 '' );

 16 end;
 17
 18
 19
 20 begin

 21      for i in 0..255 loop
 22          if ( i not between ascii('a') and
ascii('z') AND
 23               i not between ascii('A') and
ascii('Z') AND
 24               i not between ascii('0') and
ascii('9') )
 25          then
 26              g_bad_chars := g_bad_chars || chr(i);
 27          end if;
 28      end loop;
 29      g_a_bad_char := rpad(
 30                        substr(g_bad_chars,1,1),
 31                        length(g_bad_chars),
 32                        substr(g_bad_chars,1,1));

 33 end;
 34 /

Package body created.

ops$tkyte_at_8i> select
  2 utils.strip_bad( 'How is this?' ) ,   3 dump( utils.strip_bad( 'How is this?' ) )   4 from dual;

UTILS.STRIP_BAD('HOWISTHIS?')

---

---

DUMP(UTILS.STRIP_BAD('HOWISTHIS?'))

---

---

Howisthis
Typ=1 Len=9: 72,111,119,105,115,116,104,105,115

• "Deshpande, Kirti" <kirti.deshpande_at_verizon.com> wrote: > Hello Listers,
  > I was asked if there is any procedure that could be
  > called from a trigger to replace (with a blank) or
  > remove all non-printable characters from the text
  > before inserting it in the target table? The
  > incoming data is from a Web interface and may
  > contain underline, bold, blink, NL characters.
  > Target column should get characters 0-9, A-Z and
  > a-z. The data should not be rejected.
  >
  > I suggested use of 'replace', but was asked if I
  > had anything already written to do this. And I have
  > not.
  >
  > Done anyone have anything to share?
  >
  > Thanks.
  >
  > - Kirti
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.net
  > --
  > Author: Deshpande, Kirti
  > INET: kirti.deshpande_at_verizon.com
  >
  > Fat City Network Services -- 858-538-5051
  > http://www.fatcity.com
  > San Diego, California -- Mailing list and web
  > hosting services
  >
  

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  >
  

  ---

Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"

---

Do You Yahoo!?
Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: hamcdc_at_yahoo.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 03 2003 - 12:04:15 CST