Strip out "bad" characters from a result set 2

[MSIsam]

Is there a SQL sytax to strip out "bad" data from a column? By bad data, I mean tabs, carriage returns, and any non-printable characters. The Excel equivalent of the CLEAN function.

 

[BJCooperIT]

Have you tried the Replace function?

replace (string1, string_to_replace, [replacement_string] )

replace ('0000123', '0');

would return '123'

replace ('0000123', '0', 'X');

would return 'XXXX123'

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[BJCooperIT]

Sorry, omit the ";"

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[MSIsam]

Thanks BJCooperIT,
That works if I know what I need to replace. The problem is that I want to remove all non-printable characters. I want to do exaclty what the CLEAN funstion in Excel does.

CLEAN = Removes all nonprintable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system. For example, you can use CLEAN to remove some low-level computer code that is frequently at the beginning and end of data files and cannot be printed.

Thanks

 

[SantaMufasa]

MS,

Here is a "CLEAN" function I just built for you. It strips from a string any character whose ASCII code is less than "32". If you wish to strip other characters, then just concatenate the additional codes to the "strip_chrs" string in the code:

create or replace function clean (str_in varchar2) return varchar2 is
	strip_chrs	varchar2(100);
begin
	for i in 1..31 loop
		strip_chrs := strip_chrs||chr(i);
	end loop;
	return translate(str_in,'^'||strip_chrs,'^');
end;
/

Function created.

select clean('Test cleaning...['||chr(9)||chr(13)||chr(10)||']') from dual;

Test cleaning...[]

Let me know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[MSIsam]

Thanks Mufasa! That's it!!!