Subject Re: Character set
Author peter_jacobi.rm
Hi p_lerner!

--- In, p_lerner wrote:
> "set newFld = _ISO8859_1 oldFld"

Uuuh, ooh! As Helen already pointed out, the _<charset>
syntax is only for constants.

The SQL standard's idea of changing the character
set is via the widely unimplemented CREATE TRANSLATION,
so that's no use too.

On the other hand, the

set newFld = cast (oldFld as VARCHAR(70) character set ISO8859_1)

approach only fails, if the source field (with charset NONE)
contains some non ASCII characters. So, if you don't how these
are encoded, you may have a genuine data validity problem in itself
and the failing SQL is only a symptom?

But if you know these #@!%$ characters are ISO8859_1 encoded,
and only the column isn't declared so, it seems the time
for a more hackish solution. Unfortunately I cannot
suggest a good one now, but I'm working on it.

Out of curiousity, this would work, but it doesn't seem
very clever:

Define UDFs hex and unhex which transforms for strings to
and from strings containging the hexadecimal values of
theirs chars, i.e.
hex ('ABCZ') => '4142435A'
unhex ('4142435A') => 'ABCZ'

As the hex string only contains ASCII chars, charset casting will
never fail and you can use this update:

set newFld =
unhex (
cast (
hex (oldFld)
as VARCHAR(70) character set ISO8859_1

Peter Jacobi