asked on

Stripping non-printable characters from varchar2 column

I am storing all the sql generated by Informatica PowerMart ETL tool and the sql is being stored with chr(10) and chr(13) in it causing ugly viewing. I'd like to strip that out for easy viewing.

Here is an example:

SELECT DISTINCT HRA_QUESTIONNAIRE.HEALTH_RELATED_ACTIVITY_ID

FROM

HRA_QUES_ANSWER_HISTORY, HRA_Q

I've reviewed some of the other answers related to this topic and having found what I need or am not understanding how it works. Can somebody show me how to strip those two characters, maybe replacing them with spaces, and leave the rest of the text?

pratikroy

There could be different ways of doing this. Tell us how do you wish to do it ?

You can use grep, awk, sed or write a program in C, perl etc to get rid of blank lines from a file. Depends on how and where you have stored this SQL file.

ASKER CERTIFIED SOLUTION

pratikroy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pratikroy

I just got rid of the special characters, but if you wish to convert them to a space character (which I think you would want), then do this :

SQL> select translate(MYFLD,chr(13)||chr(10)||'A', ' A') from TAB_CHARS;

TRANSLATE(
----------
abc def
ghi jkl
120
180
1700
SEED

6 rows selected.

Hope this helps!

erhanyayli

Hi

You may use replace function to eliminate unwanted characters

Below should omit char(10+13)

select replace(replace(string, chr(10)),chr(13)) from dual

Regards,
Erhan

mottor

pratikroy,

You can alo delete 'A':
select translate(MYFLD,chr(13)||chr(10), ' ') from TAB_CHARS;

pratikroy

true :))

DonFreeman

ASKER

I'm not getting consistent results.

The two chrs come in pairs.
There is a pair of chr(13)||chr(10) at the end of each of these lines

SELECT HEALTH_RELATED_ACTIVITY.HEALTH_RELATED_ACTIVITY_ID
FROM
HEALTH_RELATED_ACTIVITY
WHERE

DonFreeman

ASKER

This got it. Thanks everybody!

SELECT TRANSLATE(SUBSTR(SQL_STATEMENT_TEXT,1,100),CHR(13)||CHR(10),' ') FROM RPT_SQL_STATEMENTS WHERE ROWID='AAA1IpAATAAABEeAE/';

TRANSLATE(SUBSTR(SQL_STATEMENT_TEXT,1,100),CHR(13)||CHR(10),'')
----------------------------------------------------------------------------------------------------
SELECT HEALTH_RELATED_ACTIVITY.HEALTH_RELATED_ACTIVITY_ID FROM HEALTH_RELATED_ACTIVITY WHERE

pratikroy

Glad your problem is solved !! Cheers !