Oracle-L: RE: Replacing control chars

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 03 Jan 2003 10:24:13 -0800
Message-ID: <F001.00526BFC.20030103102413@fatcity.com>

Raj,
Thanks for the response.
Input is not an HTML text, but the JSP Web page where text can be typed or "pasted" in. And the pasting from MS Word docs creates the problem.

Kirti

-----Original Message-----
Sent: Friday, January 03, 2003 12:01 PM
To: 'ORACLE-L_at_fatcity.com'; Deshpande, Kirti

Kirti,

if the input is going to be HTML, then I have no clue how you'd do it, but if it is just a varchar2 then something like following might help ...

SELECT REPLACE( UPPER(:my_str)

               ,REPLACE( TRANSLATE( UPPER(:my_str) 
                                   ,'123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' 
                                   ,'-----------------------------------' 
                                   ) 
                        ,'-' 
                        ) 
               )

FROM DUAL
/

It gets more complicated for mixed case queries. Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Friday, January 03, 2003 12:09 PM To: Multiple recipients of list ORACLE-L

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).

Received on Fri Jan 03 2003 - 12:24:13 CST