-
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 ..
Last edited by prakashs43; 02-25-2004 at 01:40 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|