Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

replace function

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
ram_0218
Asked:
ram_0218
  • 2
  • 2
  • 2
  • +5
5 Solutions
 
johnsoneSenior Oracle DBACommented:
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
 
ram_0218Author Commented:
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
 
paquicubaCommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
awking00Commented:
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
 
awking00Commented:
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
 
GGuzdziolCommented:
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
 
GGuzdziolCommented:
Ooops it's a little bit long, sorry for that :-/
0
 
johnsoneSenior Oracle DBACommented:
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
 
ram_0218Author Commented:
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
 
actonwangCommented:
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
 
actonwangCommented:
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
 
MohanKNairCommented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now