sql statement to delete control character

Hi

could you tell me sql statement to delete control characters like chr(10) chr(13) from a particular column . i don't want this to be replaced by space .

example

list
-----
abcd ..... chr(10) hidden character present
efgh

this is single record it should display abcdefgh

appreciate ur ideas.

Hi

Look at the translate function in the manuals.

something like select (chr(10),chr(10),'XYZ') from dual;

regards
Hrishy

SQL> select 'a'||chr(10)||'b' from dual;

'A'
---
a
b


SQL> select replace('a'||chr(10)||'b',chr(10),'') from dual;

RE
--
ab

Is it the one you want?

create table test
(txt varchar2(10) ) ;

insert into test values ( 'abc' || chr(10) || 'def' ) ;

insert into test values ( 'ab' || chr(10) || 'dvdv' ) ;

i need to identify position in txt and then delete that character

before
abc chr(10) def
ab chr(10) dvdv

after
abcdef
abdvdv

select replace ( instr(txt,chr(10),1), '' ) from test ;

output
4
3


update test
set txt = substr ( txt , 1 , replace(instr(txt,chr(10),1),'')-1 ) || substr ( txt , replace(instr(txt,chr(10),1),'') +1 )
where instr (txt , chr(10) , 1 ) > 0

output
-------
abcdef
abdvdv

could anybody point out some bug in the above statement .
like
1) one to one relationship is maintained
2) do i need to add anything in the where clause
3) what if the above string contains more than 1 control character of same type .


what if i find multiple characters chr(10) in a single row .
i mean

abc chr(10) def chr(10) ghi

looking for your advise ..