Re: Varying fields and SQL predicate LIKE

[CRPence <CRPbottle@xxxxxxxxx>]

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