Re: simple problem with string handling

On 25 Feb 2013 23:18, Joep Beckeringh wrote:

Hm, that is interesting. What you describe is in line with what the
SQL Reference (7.1) states,

Not that I am aware of. I did omit an additional detail: For a constant /length/ argument, the data-type result of the SUBSTR scalar is [cast to] a CHAR instead of VARCHAR. The result of the SUBSTR of the empty-string is still the empty-string, despite into what the final result is cast.

but a little test with STRSQL (7.1) seems to contradict it:

Given that foo is a char(30) column in table bar, with actual values
ranging in length from 8 to 30, the following:

select foo
, length(trim(foo))
, substr(trim(foo), 10, 30)
, length(substr(trim(foo), 10, 30)) /* <ed:> fourth /column/ */
from bar

returns 30 in the fourth column for all rows. That remains true
when foo is varchar(30), char(1) or varchar(1).

The LENGTH scalar of a CHAR effectively returns the length of the data-type, not the length of the string value of that data-type as with VARCHAR. Thus the result of the substring of the empty-string is actually the empty-string, but the CHAR representation of the empty-string in a CHAR(30) is thirty bytes of spaces\blanks; i.e. the result of the expression SUBSTR('', 1, 30) is the empty-string, but is returned as CAST('' as CHAR(30))

So the result of the SUBSTR that is inside the LENGTH() [i.e. the length(substr(trim(foo), 10, 30))] evaluated to the data-type\length of a CHAR(30), and thus the length of the result is always 30.

Even though the reference explicitly says 'If length is explicitly
specified, padding is not performed.', it appears that padding is
performed.

I am not sure where that is noted, because the following is what I found:
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscasubstr.htm
_i SUBSTR i_
"The SUBSTR function returns a substring of a string.

>>-SUBSTR--(--expression--,--start--+-----------+--)-----------><
'-,--length-'

...
_length_
...
If length is explicitly specified, expression is effectively padded on the right with the necessary number of blank characters so that the specified substring of expression always exists. Hexadecimal zeroes are used as the padding character when expression is a binary string.
..."

Although I always accepted the behaviour of the RPG compiler as
logical, I agree that the way SQL apparently handles it would have
been preferable.

I concur.

Regards, Chuck

Op 25 feb. 2013, om 19:59 heeft CRPence het volgende geschreven:

Odd that the RPG does not simply return the empty string for the
result of the %subst opcode in that example, and therefore evaluate
to false for the comparison. Any substring of a varying variable
that has the value of the empty-string is always the empty-string,
much like the product of any numeric with the zero-value is always
zero. FWiW the SQL will return the empty-string in that case using
its SUBSTR scalar function [and LEFT, RIGHT, ¿and others?].
<<SNIP>>