Re: SQL and Regular expression

I did end up using Field1 LIKE '%' || x'41' || '%'

However, I do find it is unexpected that the regular expression was
translated from x'41' ( ASCII ) to x'C1' ( EBCDIC ) for the match.

Michael




"Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
02/15/2016 01:17 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL and Regular expression






Why don't you just use Field1 LIKE '%' || x'41' || '%'

x'41' is an unprintable character. That is why the hex constant. If you
don't want the concatenation operators in there you could use Field1 LIKE
x'6c416c' buy that is a lot harder to read, and I am not certain that '%'
is invariant. Under some CCSID's, this might not work.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----mprice@xxxxxxxxx wrote: -----
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
From: mprice@xxxxxxxxx
Date: 02/15/2016 11:23AM
Subject: Re: SQL and Regular expression


When we import data from an external source, we sometimes get a 'bad'
character.

In this particular case I was looking for X'41' ( EBCDIC ) that gets sent
for a ' ' (X'40').

In other words REGEXP_LIKE(Field1,'\x41') was matching 'A'.

Michael




John Yeung <gallium.arsenide@xxxxxxxxx>
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
02/15/2016 10:30 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
Re: SQL and Regular expression






On Mon, Feb 15, 2016 at 8:34 AM, <mprice@xxxxxxxxx> wrote:

I was running an SQL statement on a field in a physical file using
REGEXP_LIKE(Field1,'\xC1') expecting to find 'A' .

After this failed to return the desired results, I discovered that I

have

to use the ASCII equivalent.

ie REGEXP_LIKE(Field1,'\x41')

Expected ?
or
Strange ?

To me, SQL is behaving in a way that is expected (or at least not
particularly strange), but what you are trying to do is very strange.
Why are you searching for hex codes?

John Y.