RE: How to recognize "foreign" characters -- MIDRANGE-L

Hi Rob
Understood - hopefully, we will be upgrading to 7.3 in the near future

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, February 15, 2018 9:33 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: How to recognize "foreign" characters

You could try opening a service request as to why it doesn't work in 7.1.

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Alan Shore <ashore@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 02/15/2018 09:03 AM
Subject: RE: How to recognize "foreign" characters
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

Locate seems to work
This is the sql
with junk as (
select concat(concat('abc', x'070707'), 'defghij') data from
sysibm.sysdummy1
union
select concat(concat('abcde', x'070707'), 'fghij') data from
sysibm.sysdummy1
union
select concat(concat('abcdefg', x'070707'), 'hij') data from
sysibm.sysdummy1
)
select data, hex(data) as Hex_date,
locate(x'07', data) found_at
from junk

Here are the results
DATA HEX_DATE FOUND_AT
abc defghij 81828307070784858687888991 4
abcde fghij 81828384850707078687888991 6
abcdefg hij 81828384858687070707888991 8

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: Alan Shore
Sent: Thursday, February 15, 2018 8:59 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: How to recognize "foreign" characters

Thanks Gord
Glad its not just me
Maybe position in SQL changed from 7.1 to 7.3

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Gord Hutchinson
Sent: Thursday, February 15, 2018 8:57 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: How to recognize "foreign" characters

I'm also getting 0 for found_at. Also on 7.1

Gord

On Thu, Feb 15, 2018 at 8:40 AM, Alan Shore <ashore@xxxxxxxx> wrote:

Thanks Rob
So it looks like that this doesn't work on 7.1

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Rob Berendt
Sent: Thursday, February 15, 2018 8:21 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: How to recognize "foreign" characters

I pasted that into Run SQL Scripts. The ONLY change I made was to put
a semicolon at the end.
Worked great.
Tried STRSQL.
Worked great.
....+....1....+....2....+....3....+....4....+....5....+..
DATA HEX_DATE FOUND_AT
abcdefg hij 81828384858687070707888991 8
abc defghij 81828307070784858687888991 4
abcde fghij 81828384850707078687888991 6

IBM i 7.3
PTF Group Level Status
SF99703 8 Apply at next IPL
SF99703 7 Installed

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

From: Alan Shore <ashore@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 02/15/2018 08:12 AM
Subject: RE: How to recognize "foreign" characters
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

Hi Coy
Well I cut and pasted you SQL into STRSQL and here is my results

DATA FOUND_AT
abc defghij 0
abcde fghij 0
abcdefg hij 0

I then slightly changed it
with junk as (
select concat(concat('abc', x'070707'), 'defghij') data from
sysibm.sysdummy1
union
select concat(concat('abcde', x'070707'), 'fghij') data from
sysibm.sysdummy1
union
select concat(concat('abcdefg', x'070707'), 'hij') data from
sysibm.sysdummy1
)
select data, hex(data) as Hex_date,
position(x'070707', data) found_at
from junk

Which displayed the following
DATA HEX_DATE FOUND_AT
abc defghij 81828307070784858687888991 0
abcde fghij 81828384850707078687888991 0
abcdefg hij 81828384858687070707888991 0
******** End of data ********

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

From: Alan Shore
Sent: Wednesday, February 14, 2018 7:21 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: How to recognize "foreign" characters

Thanks Coy
Will check this tomorrow

Sent via the Samsung GALAXY S(r) 5, an AT&T 4G LTE smartphone

-------- Original message --------
From: "Krill, Coy" <CKrill@xxxxxxxxxxx<mailto:CKrill@xxxxxxxxxxx>>
Date: 2/14/18 5:16 PM (GMT-05:00)
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx<
mailto:midrange-l@xxxxxxxxxxxx>>
Subject: RE: How to recognize "foreign" characters Try this and see if
it works, in case something is up with your system.
I'm pretty sure this worked back on 7.1 but I'm on 7.3.

with junk as (
select concat(concat('abc', x'070707'), 'defghij') data from
sysibm.sysdummy1
union
select concat(concat('abcde', x'070707'), 'fghij') data from
sysibm.sysdummy1
union
select concat(concat('abcdefg', x'070707'), 'hij') data from
sysibm.sysdummy1
)
select data, position(x'070707', data) found_at from junk;

Mine looks like this:

abcdefg hij 8
abc defghij 4
abcde fghij 6

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Alan Shore
Sent: 2018 February 14 13:58
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx<
mailto:midrange-l@xxxxxxxxxxxx>>
Subject: RE: How to recognize "foreign" characters
Importance: Low

Thanks Coy
I am playing around with the query you sent me - so far - no luck -
but its still early

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Krill, Coy
Sent: Wednesday, February 14, 2018 4:54 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx<
mailto:midrange-l@xxxxxxxxxxxx>>
Subject: RE: How to recognize "foreign" characters

That's an EBCDIC DEL character not ASCII where it's a BEL, I was
misremembering which was which. We had problems with 07 bell and 08
backspace. They didn't convert correctly to EBCDIC, I don't remember
what we had doing the conversion but it ignored control characters and
left them as is, so we had RPG III code that removed both of those

characters.

They were warranty registration cards from a service bureau that did
the data entry so I never knew why those would be embedded. Now that I
think about it, I think those came on 8" floppies too <shiver>.

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Krill, Coy
Sent: 2018 February 14 13:37
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx<
mailto:midrange-l@xxxxxxxxxxxx>>
Subject: RE: How to recognize "foreign" characters
Importance: Low

I don't know about finding foreign charactes, but you can look for hex
strings using position:

select *
from file
where position(x'070707', field) != 0

It might also be interesting to note that x07 is the delete character
and way back in the day (late '80s) I remember seeing lots of those
being stored in text strings especially when we were getting ASCII
date from some service bureau.

Coy Krill
Core Processing Administrator/Analyst
Washington Trust Bank

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Alan Shore
Sent: 2018 February 14 08:03
To: midrange-l@xxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxx>
Subject: How to recognize "foreign" characters
Importance: Low

Hi everyone
Before I forget - we are on V7r1

Our system receives orders that sometimes contains "foreign"
characters - chines etc., Arabic Looking at the data in hexadecimal,
it seems to be consistently 070707, but never in the same place or
length Is there any way (via SQL) to look for these characters within

the string?

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<
mailto:MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change
list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<
mailto:MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l

Please contact support@xxxxxxxxxxxx<mailto:support@xxxxxxxxxxxx> for
any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

---------------------------------------------------------------------
This electronic mail message and any attachments may contain
confidential or privileged information and is intended for use solely
by the above-referenced recipient. Any review, copying, printing,
disclosure, distribution, or other use by any other person or entity
is strictly prohibited under applicable law. If you are not the named
recipient, or believe you have received this message in error, please
immediately notify the sender by replying to this message and delete
the copy you received

---------------------------------------------------------------------

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<
mailto:MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change
list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<
mailto:MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l

Please contact support@xxxxxxxxxxxx<mailto:support@xxxxxxxxxxxx> for
any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<
mailto:MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change
list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<
mailto:MIDRANGE-L-request@xxxxxxxxxxxx> Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l

Please contact support@xxxxxxxxxxxx<mailto:support@xxxxxxxxxxxx> for
any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

---------------------------------------------------------------------
This electronic mail message and any attachments may contain
confidential or privileged information and is intended for use solely
by the above-referenced recipient. Any review, copying, printing,
disclosure, distribution, or other use by any other person or entity
is strictly prohibited under applicable law. If you are not the named
recipient, or believe you have received this message in error, please
immediately notify the sender by replying to this message and delete
the copy you received

---------------------------------------------------------------------

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx<
mailto:MIDRANGE-L@xxxxxxxxxxxx> To subscribe, unsubscribe, or change
list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx<
mailto:MIDRANGE-L-request@xxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx<mailto:support@xxxxxxxxxxxx> for
any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
Gord Hutchinson
TST Overland Express
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD