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.
Using Char(254) and field function
Moderators: chulett, rschirm, roy
Using Char(254) and field function
Bob Oxtoby
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
Bob Oxtoby
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.
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)
Choose a job you love, and you will never have to work a day in your life. - Confucius