Remove special Character 1

[IanWaterman]

We have a field VARCHAR2(2000) into which users have started using a special character. I think its a return.

This is not a problem until we try to do our nightly data extract for a warehouse. File exported is pipe delimited txt.

This special char introduces a line into our txt file causing import to fail.

How do I determine what the special character is?
How do I remove it? Can I use REPLACE() in the extraction query?

Thank you

Ian

 

[Dagon]

Here's an example of the sort of thing you need:

SQL> drop table specchar
Table dropped.
SQL> create table specchar (text varchar2(250))
Table created.
SQL> insert into specchar values ('x
y')
1 row created.
SQL> select ascii(substr(text,2,1)) from specchar

ASCII(SUBSTR(TEXT,2,1))
-----------------------
                     10
1 row selected.
SQL> update specchar set text = replace(text, chr(10), null)
1 row updated.
SQL> select text from specchar

TEXT       
-------
xy                                                                              
1 row selected.

For Oracle-related work, contact me through Linked-In.

 

[IanWaterman]

Dagon

Thanks that did the trick ended up having to repeat replace 3 times

replace(replace(replace(ATA_NARRATION, chr(13), null), chr(10), null), chr(9), null) TRX_DESCRIPTION

Ian

 

[Dagon]

You could also have used a regular expression to get rid of the whole lot in one go:

select regexp_replace('x'||chr(10)||chr(9)||chr(13)||'y', chr(10)||'|'||chr(9)||'|'||chr(13), '') from dual

For Oracle-related work, contact me through Linked-In.