Re: Help on Removing "Junk" Characters

From: JTommaney <cdos_jtommaney_at_comcast.net>
Date: 18 Oct 2005 09:20:25 -0700
Message-ID: <1129652425.355691.43610@g44g2000cwa.googlegroups.com>

Here is one approach using translate, there certainly may be other approaches.
The keep list defined here is a-z, A-Z, 0-9, and ' ' (space). This will not trim repeated spaces

As always, test before using: Credit goes to asktom.oracle.com for the

select chr(level) l_char, level lvl from dual connect by level<=255 syntax.

create or replace function keep_chars return string as
keep_chars varchar2(255);
begin
keep_chars := '';
for x in ( select l_char from (select

	chr(level) l_char, level lvl from dual
	connect by level<=255)
	where lvl between 48 and 57
	or lvl between 65 and 90
	or lvl between 97 and 122
	or lvl = 32 ) loop
	keep_chars := keep_chars || x.l_char;

end loop;
return keep_chars;
end;
/

create or replace function drop_chars return string as
drop_chars varchar2(255);
begin
drop_chars := '';
for x in ( select l_char from (select

	chr(level) l_char, level lvl from dual
	connect by level<=255)
	where not( lvl between 48 and 57
	or lvl between 65 and 90
	or lvl between 97 and 122
	or lvl = 32) ) loop
	drop_chars := drop_chars || x.l_char;

end loop;
return drop_chars;
end;
/

--drop table test1;

create table test1 as
select 'Pericles Comidas cl?cas Guillermo Fern?ez ' text from dual

union all select 'Queen Cozinha L?cia Carvalho ' from dual
union all select 'Supr?s delices ' from dual
union all select 'START' || drop_chars || 'END' from dual
union all select 'START' || reverse(drop_chars) || 'END' from dual

union all select keep_chars from dual;

select 	translate(text,   keep_chars || drop_chars ,  keep_chars)
	translated_text from test1;

Received on Tue Oct 18 2005 - 11:20:25 CDT