On 22-Sep-2011 08:24 , John McKee wrote:
Is there a way to scan a text field in a file for an embedded
character less than an EBCDIC blank? <<SNIP>> wondered if SQL could
be used - just to see if any others are present. <<SNIP>> looking
for a (relatively) quick way to determine if other invalid characters
have been written to the file and those accounts just have not been
displayed yet.
I thought about using query, but I am not looking for a specific
character - just one less than x'40' if it exists at all.
Using SRTSEQ(NONPRT2SUB) where that named sort sequence table [object
type *TBL has been defined to translate\convert all characters x'3F' or
less to the code point x'3F', e.g. by CRTTBL TBL(NONPRT2SUB)
SRCFILE(*PROMPT) TBLTYPE(*CVT) BASETBL(*HEX), the following query will
find all relative row numbers [while also giving the hex string value of
the text] for any row where the text column has any character code point
value less than the 0x40 EBCDIC blank.
<code>
/* run using SRTSEQ(NONPRT2SUB) */
select rrn(d)
, hex(textcolumn)
, locate(x'3F',textcolumn)
from datafile d
where locate(x'3F',textcolumn)<>0
</code>
Or, without having to create and use a previously-created [conversion
or] sort sequence table, each of the following SQL statements will
effect the equivalent of the prior example by similar means; i.e. using
the SQL scalar function TRANSLATE(), instead of the sort sequence support:
<code>
select rrn(d) /* and optionally the next two expressions */
, hex(textcolumn)
, locate( x'3F' /* Find 0x3F in following xlated string */
, translate( textcolumn
, x'3F'
,
x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'
, x'3F' /* pad xlate-out string */ )
)
from datafile d
where textcolumn
<> translate( textcolumn
, ' '
,
x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'
, ' ' )
select rrn(d) /* and optionally the next two expressions */
, hex(textcolumn)
, locate( x'3F' /* Find 0x3F in following xlated string */
, translate( textcolumn
, x'3F'
,
x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'
, x'3F' /* pad xlate-out string */ )
)
from datafile d
where locate( x'3F' /* Find 0x3F in following xlated string */
, translate( textcolumn
, x'3F'
,
x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'
, x'3F' /* pad xlate-out string */ )
)<>0
with
unprt ( rn, textcolumn, loc3f ) as
( select rrn(d)
, hex(textcolumn)
, locate( x'3F' /* Find 0x3F in following xlated string */
, translate( textcolumn
, x'3F'
,
x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'
, x'3F' /* pad xlate-out string */ )
)
from datafile d
)
select u.*
from unprt u
where loc3f<>0
</code>
Regards, Chuck
midrange.com
