On 8/4/06, Dirk Moolman <DirkM@digicare.co.za> wrote:
>
> My own problem is not removing these characters [^M] from the unload file,
> but removing them from the table before I do the unload.
>
> Any ideas ?



First, consider whether the question is well phrased - I debated whether to
respond to your previous question, and decided not to until I saw this
follow-up. See:
http://www.catb.org/~esr/faqs/smart-...html#symptoms- that's
more precise than the normal blanket URL.


If you want to remove characters from a table before you do an unload, you
must mean you want to update the data in the table so that it no longer
contains those characters. Or do you mean something else?

Also, when you say UNLOAD, you presumably do not mean a regular
Informix-provided UNLOAD statement since that handles ^M quite happily.

This sort of text manipulation is, in my view, best done outside the
database. I'd probably use Perl + DBI + DBD::Informix to deal with it.
Under the 'update' theory, I'd select the data, edit it, and then update the
data in the database. Given that you're using blobs, IIRC, I don't think
there's an easy way to filter the data based on whether it contains ^M or
not.

If you want to do it truly in the DBMS, you're probably looking at some
really messy code - doubly so if it is BYTE/TEXT blobs rather than BLOB/CLOB
blobs. You're likely to need a UDR of some sort, probably not in SPL (so
Java or C).

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/