Solved

# replace function

Posted on 2006-05-26
526 Views
guys,

i've a hard time at googling! i want to know how to replace a line which has loads of non printable binary characters and want to extract only those printable characters. is there anyway to do this with replace function or do we have any other functions to do this?
0
Question by:ram_0218

LVL 34

Expert Comment

What about a simple funciton like this:

create or replace function remove_unprint (str_in varchar2) return varchar2 as
str_out varchar2(4000);
begin
str_out := null;
for i in 1..length(str_in) loop
if ascii(substr(str_in, i, 1)) between 27 and 127 then
str_out := str_out || substr(str_in, i, 1);
end if;
end loop;
return str_out;
end;
/

You may want to redefine what a printable character is, but characters 27 through 127 are generally regarded as printable.
0

LVL 17

Author Comment

Johnson, thanks for the tips. But I'm sorry to say this, this is what we've been doing right now. I'm just wondering if we have any thing else a direct function using replace.
0

LVL 23

Expert Comment

PAQUI@PROD > select chr(10)||chr(13)||'Alex' from dual;

CHR(10
------

Alex

Elapsed: 00:00:00.01
PAQUI@PROD > select REGEXP_REPLACE(chr(10)||chr(13)||'Alex','[^[:print:]]','') from dual;

REGE
----
Alex
0

LVL 31

Accepted Solution

I generally consider the printable characters as having ascii values from 32 to 126. You can also write your function using the replace function as follows:

create or replace function get_print_chars(str_in in varchar2) return varchar2 is

v_str varchar2(4000);
begin
v_str := str_in;
for i in 1..31 loop
v_str := replace(v_str,chr(i),'');
end loop;
v_str := replace(v_str,chr(127),'');
return v_str;
end;
/
0

LVL 31

Expert Comment

I didn't see your comments before submitting my reply. The only way I can see your using a "direct" function would be to nest the replace function instead of putting it in a loop as in my suggestion, but that would get pretty cumbersome.
(i.e. select replace  ... replace(replace(str,chr(1),''),chr(2),'') ... ,chr(31),''), etc.)
0

LVL 14

Assisted Solution

Maybe something like

select replace(
translate(
:x,
CHR(1) ||
CHR(2) ||
CHR(3) ||
CHR(4) ||
CHR(5) ||
CHR(6) ||
CHR(7) ||
CHR(8) ||
CHR(9) ||
CHR(10) ||
CHR(11) ||
CHR(12) ||
CHR(13) ||
CHR(14) ||
CHR(15) ||
CHR(16) ||
CHR(17) ||
CHR(18) ||
CHR(19) ||
CHR(20) ||
CHR(21) ||
CHR(22) ||
CHR(23) ||
CHR(24) ||
CHR(25) ||
CHR(26) ||
CHR(128) ||
CHR(129) ||
CHR(130) ||
CHR(131) ||
CHR(132) ||
CHR(133) ||
CHR(134) ||
CHR(135) ||
CHR(136) ||
CHR(137) ||
CHR(138) ||
CHR(139) ||
CHR(140) ||
CHR(141) ||
CHR(142) ||
CHR(143) ||
CHR(144) ||
CHR(145) ||
CHR(146) ||
CHR(147) ||
CHR(148) ||
CHR(149) ||
CHR(150) ||
CHR(151) ||
CHR(152) ||
CHR(153) ||
CHR(154) ||
CHR(155) ||
CHR(156) ||
CHR(157) ||
CHR(158) ||
CHR(159) ||
CHR(160) ||
CHR(161) ||
CHR(162) ||
CHR(163) ||
CHR(164) ||
CHR(165) ||
CHR(166) ||
CHR(167) ||
CHR(168) ||
CHR(169) ||
CHR(170) ||
CHR(171) ||
CHR(172) ||
CHR(173) ||
CHR(174) ||
CHR(175) ||
CHR(176) ||
CHR(177) ||
CHR(178) ||
CHR(179) ||
CHR(180) ||
CHR(181) ||
CHR(182) ||
CHR(183) ||
CHR(184) ||
CHR(185) ||
CHR(186) ||
CHR(187) ||
CHR(188) ||
CHR(189) ||
CHR(190) ||
CHR(191) ||
CHR(192) ||
CHR(193) ||
CHR(194) ||
CHR(195) ||
CHR(196) ||
CHR(197) ||
CHR(198) ||
CHR(199) ||
CHR(200) ||
CHR(201) ||
CHR(202) ||
CHR(203) ||
CHR(204) ||
CHR(205) ||
CHR(206) ||
CHR(207) ||
CHR(208) ||
CHR(209) ||
CHR(210) ||
CHR(211) ||
CHR(212) ||
CHR(213) ||
CHR(214) ||
CHR(215) ||
CHR(216) ||
CHR(217) ||
CHR(218) ||
CHR(219) ||
CHR(220) ||
CHR(221) ||
CHR(222) ||
CHR(223) ||
CHR(224) ||
CHR(225) ||
CHR(226) ||
CHR(227) ||
CHR(228) ||
CHR(229) ||
CHR(230) ||
CHR(231) ||
CHR(232) ||
CHR(233) ||
CHR(234) ||
CHR(235) ||
CHR(236) ||
CHR(237) ||
CHR(238) ||
CHR(239) ||
CHR(240) ||
CHR(241) ||
CHR(242) ||
CHR(243) ||
CHR(244) ||
CHR(245) ||
CHR(246) ||
CHR(247) ||
CHR(248) ||
CHR(249) ||
CHR(250) ||
CHR(251) ||
CHR(252) ||
CHR(253) ||
CHR(254) ||
CHR(255)
,
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1) ||
CHR(1)
), CHR(1)) x from dual

To generate I used

select 'CHR(' || TO_CHAR(i) || ') ||'
from (select * from (select level i from dual connect by level < 256))
where not (i between 27 and 127)

select 'CHR(' || TO_CHAR(1) || ') ||'
from (select * from (select level i from dual connect by level < 256))
where not (i between 27 and 127)

and I assumed 27-127 printable chars
0

LVL 14

Expert Comment

Ooops it's a little bit long, sorry for that :-/
0

LVL 34

Assisted Solution

Apart from a custom function, I would say the regular expression replace that paquicuba is suggesting is your best option, as long as you are on 10g.

If not, the old replace and translate may do the trick for you.  Something like this, however, you need to define all the "unprintable" characters:

select dump(replace(translate('abc' || chr(11) || 'def', chr(1) ||
chr(2) ||
chr(3) ||
chr(4) ||
chr(5) ||
chr(6) ||
chr(7) ||
chr(8) ||
chr(9) ||
chr(10) ||
chr(11) ||
chr(12) ||
chr(13) ||
chr(14) ||
chr(15) ||
chr(16) ||
chr(17) ||
chr(18) ||
chr(19) ||
chr(20) ||
chr(21) ||
chr(22) ||
chr(23) ||
chr(24) ||
chr(25) ||
chr(26),
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11) ||
chr(11)), chr(11))
from dual
;

Basically translate all unprintable characters to one unprintable character, then replace that one unprintable character.

0

LVL 17

Author Comment

paq,

your solution is elegant and I think you already taught me that !

but i'm being in 9i and the other methods suggested by others look scary! sorry!

lemme do a research more on this and if i get anything will update you guys!
0

LVL 19

Assisted Solution

hi, ram_0218,

here is simple and manageable way for you to do it, assume all printable character are between 27 and 127. You could create a simple function to get a string to include all non printable characters.

create or replace function get_noprint_str
return varchar2
as
np_str varchar2(255);
begin
for i in 1..255
loop
if not i between 27 and 127
then
np_str := np_str || chr(i);
end if;
end loop;

return np_str;
end;
/

0

LVL 19

Expert Comment

then, in your table, you could do as:

select translate(<your_col>,'a' || get_noprint_str(),'a') from you_table
/

or

select translate(<your_col>,t.str,'a') "col with printable characters"
from your_table, (select 'a' || get_noprint_str() str from dual) t
/

Enjoy :)
Acton
0

LVL 16

Assisted Solution

I don't think you will be able to find a better solution than a custom function as discussed in some of the answers above. Using replace is too complicated and take much more time than a custom function.
0

LVL 1

Expert Comment

Forced accept.

Computer101
0

## Featured Post

### Suggested Solutions

top-performance 5 88
jobsrunning 3 55
Password Hashing vs Encryption for long term viability 3 71
Salary Amount Format 13 50
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup