Re: SQL and Regular expression

["Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>]

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.