Re: Varying fields and SQL predicate LIKE

On 22-Jul-2010 09:28, Vern Hamberg wrote:

First blush - do a DSPPFM F10 F11 and verify that the length in
the first 2 bytes actually reflects the trimmed length. When I've
needed to change to varying in the DB, I've used the STRIP
function in an SQL UPDATE statement with no WHERE clause
- unless you wanted to use a condition that
length(field)<> length(strip(field))!

Take care not to confuse the SQL scalars STRIP and TRIM with the
SQL scalar RTRIM; RTRIM and LTRIM having their own peculiarities.
Both STRIP and TRIM [the same function by two distinct names]
default to remove both leading and trailing. The latter expression
of the above quoted comparison, likely would be more desirable
written instead, as one of:

length( STRIP( field, T /* , ' ' */ )

length( TRIM( TRAILING ' ' FROM field ) )

http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscartrim.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscastrpfun.htm
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscatrimfun.htm

Regards, Chuck