On May 26, 3:18 pm, Michael Krzepkowski <mkrzepkow...@gmail.com>
wrote:

If the data is already in the database, then you'll need to select the
bad records and replace them with the stripped out characters.
Oracle 10g and later has a regex function which you could use to find
non-printable "white space" within a string. (Your EOL, Ret, etc ...)

I believe there is a regex function in IDS, if not, you can write one
in C or Java as a UDR/UDF that could be used to strip out the data via
an SQL statement, or you could have a before insert trigger call the
function passing in the field and then returning the cleansed data to
be inserted.

The reason I would recommend this over a sed/awk or python script is
that by putting this in the engine, you allow it to be used by other
applications or non-sever centric sources of data. (An example would
be allowing the app to be run on a pc and the data is local to the pc
and not the server.)

HTH

-G

I went to the IIUG software area, and all it has were links to:
IDS-11.5
OAT
Server Studio
Sentinel
Member Area Home

So where can I find ascii.tgz ??

Thanks...

Rob
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of Jonathan Leffler
Sent: Tuesday, May 27, 2008 7:54 AM
To: informix-list@iiug.org
Subject: Re: Non-printable sign

On May 26, 2:23 am, DeMi <michal.dem...@gmail.com> wrote:

It strikes me that replacing the newlines with ASCII NUL '\0' would
not be a good idea; that would mark the end of the string and
effectively truncate anything after the newline.  You would probably
be better of replacing it with a blank.

For mechanics, you should look at the REPLACE function - assuming you
have a sufficiently recent version of IDS.  There is also an ASCII
function in IDS (11.50 at least, but I believe at least some earlier
versions).  There doesn't seem to be an ORD() or CHR() function built
in -- odd, I thought it was added at the same time as ASCII (it should
have been).

If you poke around the IIUG web site, you should find a package
ascii.tgz containing a now redundant ASCII function and a still
relevant CHR function plus the data for a table which those functions
use - and a script to assemble it all.  (Contents: ascii.sql,
ascii.unl, asciitbl.sql, chr.sql, mkascii.sql)  If you really can't
find that after some searching, contact me.  You'd then use CHR(10) in
the REPLACE function search string.  There are probably other ways of
doing it too.

-=JL=-
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list