Re: Unwanted funky charecters in DB

From: Ed Prochak <prochak_at_my-deja.com>
Date: Fri, 17 Dec 1999 18:24:09 GMT
Message-ID: <83dv43$3rs$1@nnrp1.deja.com>

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. Received on Fri Dec 17 1999 - 12:24:09 CST