Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2087
  • Last Modified:

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?

0
DonFreeman
Asked:
DonFreeman
1 Solution
 
pratikroyCommented:
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.
0
 
pratikroyCommented:
oops ... did'nt see tha you have this stored in a varchar2 column.

well, if you just wish to ignore the chr(10) and chr(13) you can do this :

SQL> desc TAB_CHARS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MYFLD                                              VARCHAR2(10)

SQL> select * from TAB_CHARS;

MYFLD
----------
120
180
1700
SEED

SQL> insert into TAB_CHARS values ('abc' || chr(10) || 'def');

1 row created.

SQL> insert into TAB_CHARS values ('ghi' || chr(13) || 'jkl');

1 row created.

SQL> select * from TAB_CHARS;

MYFLD
----------
abc
def

jkl
120
180
1700
SEED

6 rows selected.

SQL> select translate(MYFLD,chr(13)||'A', 'A') from select translate(MYFLD,chr(13)||'^C

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

TRANSLATE(
----------
abcdef
ghiAjkl
120
180
1700
SEED

6 rows selected.

Hope this helps!
0
 
pratikroyCommented:
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!
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
erhanyayliCommented:
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
0
 
mottorCommented:
pratikroy,

You can alo delete 'A':
select translate(MYFLD,chr(13)||chr(10), '  ') from TAB_CHARS;
0
 
pratikroyCommented:
true :))
0
 
DonFreemanAuthor Commented:
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

0
 
DonFreemanAuthor Commented:
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
0
 
pratikroyCommented:
Glad your problem is solved !! Cheers !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now