RE: LEFT vs SUBSTR

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

No, did it on V5R3.

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.

Mystery solved.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com
 

-----Original Message-----
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?

Rob Berendt