Re: REGEXP_INSTR range

[Peter Dow <petercdow@xxxxxxxxx>]

Thanks Arnie.  I did actually try both x'3f' and x'3F' and neither one 
worked.

I think \xhh is just hex.  A byte is an 8-bit number, with values from 
0-255; hex is just a convenient way of writing those numbers.  Each 4 
bits is a number from 0-15, which in hex is 0-F 
(0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F).  Put the hex values for the two 4-bit 
numbers together and you've described the value of a single 8-bit byte.

But you are correct in that I'm dealing with EBCDIC, and for my 
purposes, I want to identify any byte that has a values less than x'40' 
(decimal 64), and that's the range I'm specifying (I think) with 
[\x00-\x3F].  In EBCDIC, those are control characters and other 
non-printable characters.

You did inspire me to test a little more thoroughly.  Here's what I tried:

values regexp_instr('abcdef-ghijk' || x'3f', '[\x00-\x3f]') returns 7, 
which is what I expected.

values regexp_instr('- - - - - - - - - - -', '[\x00-\x3F]') returns 1, 
which is NOT what I expected.

And in case you were wondering,

values hex('- - - - - - - - - - -')

VALUES
604060406040604060406040604060406040604060
********  End of data  ********

And both of the following return 'false':

values case when x'60' < x'3f' then 'True' else 'false' end

values case when x'60' between x'00' and x'3f'
            then 'True' else 'false' end

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 8/15/2021 4:32 AM, Arnie Flangehead wrote:
> I think I inadvertently gave you a bum steer. I was thinking about
> character collating rather than the underlying hex.
>
> However, I may be able to redeem myself. I think the \xhh is actually ASCII
> hex rather than EBCDIC hex.
>
> Reason I say that is that if you look at a conversion table such as this one
> <https://www.ibm.com/docs/en/xl-fortran-aix/16.1.0?topic=appendix-ascii-ebcdic-character-sets>
> you'll see that, for instance, capital A to G is ASCII hex 41 to 47, and if
> you try this in SQL:
>
> with foo(bar) as (
> values('ABCDEFG'))
> SELECT foo.bar,
>         regexp_instr(foo.bar,'[\x43-\x44]')
> FROM foo
>
> You get position 3, being ASCII hex'43' C, whereas EBCDIC hex'43' is: ä
> (lower-case a with umlaut, in case it doesn't show properly in the browser).
>
> If it's any help I have an unprintable character finder UDF I'm happy to
> post (gives the hex value, the display value and the position in the
> string).