Thanks Connor.
I think this might do it..
- Kirti
-----Original Message-----
Sent: Friday, January 03, 2003 12:04 PM
To: Multiple recipients of list ORACLE-L
>From AskTom
ops$tkyte@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@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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Deshpande, Kirti
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
