Re: REGEXP_INSTR range

Hi Justin,

Yes, that's significant when dealing with ranges.  It's also a clue that the REGEXP_ functions are not dealing with EBCDIC. There's much more to the thread, so if you're interested, keep reading.

REGEXP_INSTR was useful in testing how these functions work since it indicates where the the found character is located; REGEXP_LIKE just says it found it somewhere.  For cleaning up data, REGEXP_REPLACE is more appropriate.

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

/
On 8/17/2021 11:12 AM, Justin Taylor wrote:

The docs say " The characters to include are determined by Unicode code
point ordering". Is that significant?

I doubt it would matter, but wouldn't REGEXP_LIKE be more appropriate?


date: Fri, 13 Aug 2021 16:59:31 -0700

from: Peter Dow <petercdow@xxxxxxxxx>
subject: REGEXP_INSTR range

I'm trying to locate rows in a table that have any character in a
particular field with a value less than x'40'.

WHERE REGEXP_INSTR(LOGTEXT,'[\x00-\x3f]') > 0

Keeps finding records with x'60' in the field.

I concocted the regex pattern from what I read on this page:


https://www.ibm.com/docs/en/i/7.3?topic=predicates-regexp-like-predicate#rbafzregexp_like__regexp_likecontrol

but apparently I don't understand it.? What am I doing wrong?

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