Sounds to me like you're somehow creating VARCHAR fields by using SUBSTR in
SQL. Those two bytes are no control bytes, rather they are a short integer
value specifying how long the field is (how many chars it has).

If that's truly the case, remedy is simple, CAST your substring to a CHAR.
You can do it by using an explicit CAST keyword, i.e.:

CAST(SUBSTR(myBigfield, 10, 5) AS CHAR(5))

Or you can try implicit cast, i.e.:

CHAR(SUBSTR(myBigField, 10, 5))

All that said, I'm a bit confused by your distinction between "a whole field
that already existed" and field you "built".

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400

-----Original Message-----
Subject: building new fields using substr in sql

I have been substring data from pieces of
fields and outputting the fields to a physical file
created new by the sql run

I keep getting 2 extra positions of "non-diplayable"
data in front of any field I "built", but not in front
of a whole field that already existed,

this causes the desired record lenth to be off
by multiples of 2 (for each occurrance of a substr'd field)

How do I get around those control bytes so they do
NOT add to my record length.

thanks in advance for your help.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].