Re: strsql how to cram fields together

The documentation you site gives an explicit statement that the length will be that of the ORIGINAL character string. See ...same as the length attribute... here.

"The result of the function is a varying-length string with a length attribute that is the same as the length attribute of expression"

For this reason, I prefer SUBSTR, as Eric does. The problem with SUBSTR arises most when you want to get some rightmost characters.

TRIM or STRIP are also very useful in this context - Joel could have put TRIM around his LEFT and got what he wants, I think.

Fewer words - I think it's also correct! Whee!

Cheers
Vern

On 3/28/2014 4:22 PM, CRPence wrote:

On 28-Mar-2014 13:35 -0700, Stone, Joel wrote:

Why doesn't LEFT(fieldname,10) shorten the field for display?

The report writer is presenting the data as the result of the
expression, according to the data type\length. In this case,
effectively the same as the DDL for "fieldname".

The result of the LEFT scalar is atypical for what IMO, most would
desire. The result is effectively the same data type as the
_expression_ that is the first argument, but with the *length* attribute
of that _expression_, *not* the _length_ specified as the second
argument; "effectively", because the type is the variable-length
variation on that data type. The length merely requests how much of the
data should be picked [substring\substrung] from the expression, and
full data typing must be explicitly specified additionally. For example:
VARCHAR(RTRIM(LEFT(fieldname, 10)), 15)

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscaleft.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Built-in functions -> Scalar functions
_LEFT_
"The LEFT function returns the leftmost integer characters of expression.

>>-LEFT--(--expression--,--integer--)-------------------><

...

The result of the function is a varying-length string with a length
attribute that is the same as the length attribute of expression and a
data type that depends on the data type of expression:
..."

When I code CHARACTER_LENGTH(LEFT(fieldname,10)) it returns the
shorter length of 10 instead of the orig field length of 100.

At first blush, I infer that is a defect. However, there is a
documented ambiguity for results of padded varying-length string data,
so that may cover the result; i.e. sometimes the blank pad are
significant, and other times they are not, and the onus is on the user
to ensure consistently trimmed results to avoid /unpredictable/ results.

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscacharlenf.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Built-in functions -> Scalar functions
_CHARACTER_LENGTH_
"The CHARACTER_LENGTH or CHAR_LENGTH function returns the length of a
string expression.

>>-+-CHARACTER_LENGTH-+--(--expression--)---------------><
'-CHAR_LENGTH------'

...

If expression is a character string or graphic string, the result is the
number of characters in the argument (not the number of bytes). A single
character is either an SBCS, DBCS, or multiple-byte character. If
expression is a binary string, the result is the number of bytes in the
argument. The length of strings includes trailing blanks or hexadecimal
zeroes. The length of a varying-length string is the actual length, not
the maximum length.
..."