After your email I went to V6R1 memo to users and BINGO, there is a change
to the behavior of LEFT. Good thought Rob!
Here's the relevant text:
Changes to LEFT and RIGHT Scalar functions
LEFT and RIGHT SQL Scalar functions are now character based instead of byte
based. The second argument now indicates the number of characters instead of
the number of bytes. This change does not affect LEFT and RIGHT functions
where the first argument is a single-byte CCSID (for example, 37 or 500).
This change only affects the result of LEFT and RIGHT functions where the
first argument is a mixed-byte CCSID, UTF-8, or UTF-16. In the SELECT LEFT
statement below, assume that FIRSTNAME is a VARCHAR(12) column, encoded in
Unicode UTF-8, in T1. One of its values is the 6-character string Jürgen:
SELECT LEFT(FIRSTNAME, 2) FROM T1 Before V6R1, the above statement returns
the value Jô (x?4AC3?) (because 2 means 2 bytes). In V6R1, the above
statement returns the value Jü (x?4AC3BC?) (because 2 means 2 characters).
I bet you this text is what prompted that InfoCenter example as well, only
here it's explained much better.
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
Subject: RE: LEFT vs SUBSTR
And you tested this on V6R1, Elvis? After all, the section posted was
from the V6R1 infocenter and was flagged as new to that version. Perhaps
it is a change on that release?
This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact