RE: strsql how to cram fields together

OK substr works as I would expect. Thanks

The confusion (for me) was that in Paul Conte's SQL/400 book [great book], it shows Left(string,length) is identical to substr(string,1,length).

Maybe that is accurate only if string is NOT var length???



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Tuesday, April 01, 2014 5:38 PM
To: Midrange Systems Technical Discussion
Subject: Re: strsql how to cram fields together

Hi Joel

I think I read your posts as concatenating the values somehow - that
DOES work with TRIM or STRIP - did I "cast" your question this way? :)

SUBSTR also will work, in a way similar to CAST. I always used SUBSTR
when looking at this table.

substr(docid, 1, 20)

I believe the DOCID is varying length - the docs (did you look at them?)
for character_length say this -

"The length of a varying-length string is the actual length, not the
maximum length."

Now it appears that the LEFT function uses the length attribute, not the
character length. What strikes me as odd is, it seems the LENGTH
function behaves as does CHARACTER_LENGTH for varying length columns.

Just for fun, try adding LENGTH(RTRIM(LEFT(DOCID,20))) to your select
and see what you get.

I think the SUBSTR or CAST solution is best - RTRIM(LEFT(whatever,n)) is
just too messy for my taste - but taste varies, right?

HTH
Vern

On 4/1/2014 5:02 PM, Stone, Joel wrote:

Vern: the first column is STILL displaying length 100 using your suggestion. (Cast does work though).



select rtrim(left(docid,20)),left(title,30) title
from rjsimage/docs00
where title like 'Amendment F%'
SELECT statement run complete.


I STILL think it odd that the following shows the character_length as 20, but the column displays as 100 wide.

select rtrim(left(docid,20)),
character_length(rtrim(left(docid,20))),
left(title,30) title
from rjsimage/docs00
where title like 'Amendment F%'


....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+...12....+...13.
RTRIM CHARACTER_LENGTH TITLE
AS400DOC-00000000085 20 Amendment F
AS400DOC-00000000098 20 Amendment F
AS400DOC-00000000374 20 Amendment F



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Friday, March 28, 2014 6:17 PM
To: Midrange Systems Technical Discussion
Subject: 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.
..."