Re: Unwanted funky charecters in DB

 You may also want to look at the DUMP() function. It will show you which non-printable characters are stored in columns.

SELECT DUMP(ename) FROM emp;

Phil Bolduc
North Vancouver, BC Canada

In article <83dv43$3rs$1_at_nnrp1.deja.com>,   Ed Prochak <prochak_at_my-deja.com> wrote:
> In article <83bfss$e7l$1_at_nnrp1.deja.com>,
> mrs_gleeson_at_my-deja.com wrote:
> > Hi,
> > I have an unclean data stored in oracle DB. It contains many
> > undisplyable characters that reflect the wrong ASCII decimal. For
> > example the correct ASCII (' ') is 32 but when I do ASCII on this
> > invisible character I would get 0.
> > I need to create a stored procedure that will take fields from the
DB.
> > Search them for the unwanted characters, clean it up and re-post the
> > cleaned data back in the DB.
> > I don't know how many or what type of invisible characters that are
> > stored. I know some of the unwanted characters are visible for
viewing
> > when I do select in SQL but the uses can't see them in the forms.
> > Help please.
>
> something on your data input side is playing fast and loose with the
> length of strings (not setting it properly) OR is allowing control key
> and function key characters into your data entry fields. Since you are
> seeing ASCII nuls, I'd bet you have a C program that is doing this.
>
> To fix it, have a look at the TRANSLATE() function. So your procedure
> will look something like this:
>
> controlchars := CHR(09) ||
> CHR(00) ||
> CHR(27) ;
> --- <add any other control characters you want to get rid of>
>
> newchars := ' .!'; --- one replacement char for each source char
>
> update table XXX
> set column_a = translate(column_a,controlchars,newchars);
>
> this example replaces ASCII tab (09), nul (00), and escape (27)
> with a space, a period, and an exclamation mark, respectively.
> If you want all to go to spaces, just set: newchars:= ' ';
>
> (Note: the above code not tested. but should provide the correct
idea.)
>
> --
> Ed Prochak
> Magic Interface, Ltd.
> ORACLE Development, conversions, training and support
> 440-498-3700 magic_at_interfacefamily.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 17 1999 - 13:38:50 CST