Using Char(254) and field function

[boxtoby]

I am getting some very strange behaviour when using the field function with char(254) as the separator and I wondered if anyone could shed some light on the problem.

Here is the data:

Col1
Debt Consolidation~~2~448

Char(254) is represented by the tilde character for clarity

If I use the following expression field(col1, char(254), 1) I get "Debt" returned instead of "Debt Consolidation".

If I use the following expression field(col1, char(32), 1) I also get "Debt" returned which I would expect.

When I output column positions 5 (space after "Debt") and 19 (tilde after "Consolidation) I get char(32) and char(254) as expected.

So why is the field function using char(254) behaving as if it's using char(32)?


Look forward to hearing from you.


Many thanks,
Bob.

[ray.wurlod]

Just out of curiosity, what is the behaviour when you use system variable @FM rather than Char(254)?

[boxtoby]

Hi Ray,

I tried that, but the parallel transformer doesn't recognise @FM.

We also have NLS turned on and the following dsenv settings:

LANG=en_US.UTF-8
LC_ALL=en_US.UTF-8
NLS_LANG=AMERICAN_AMERICA.AL32UTF8

I have also viewed the data in hex and it appears exactly as I have described so I can't see that NLS is causing the problem.


Thanks,
Bob

[qt_ky]

This should shed some light on the problem...

This has to do with the Char() function. It must have originally been coded to handle only 7-bit ASCII characters.

When you give it a value outside that range, it defaults to using a space rather than aborting or warning.

Try using what the product documentation refers to as the allow8bits argument for 8-bit ASCII characters.

It is not very well documented as to exactly how to use that optional argument. It is expecting an expression that evaluates to a true or false value.

Code: Select all

Char(254, @TRUE)