RE: sql to scan and substring

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Be careful with POSSTR at least if you are working with UTF-8 strings (and I
expect the JSON data are in UTF-8).
POSSTR operates on a strict byte-count basis without regard to single-byte
or double-byte characters.

> From the SQL Reference:
The POSSTR function accepts mixed data strings. However, POSSTR operates on
a strict byte-count basis without regard to single-byte or double-byte
characters.
It is recommended that if either the search-string or source-string contains
mixed data, POSITION should be used instead of POSSTR. 
The POSITION function operates on a character basis.

Instead of POSITION you may use LOCATE which is also operating on a
character basis

Mit freundlichen Grüßen / Best regard

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect  
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Vern
Hamberg via MIDRANGE-L
Sent: Tuesday, 3 February 2026 15:30
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>
Subject: Re: sql to scan and substring

Hi Jim

I sometimes use the POSSTR function to get where a string is, along with
adding to the returned value to get the end or start of the next thing.

Now if values and all are broken apart and on different rows, maybe you
could put them back together with LISTAGG. That function does need something
on each row to identify what should go together.

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.U4VznBDg.CfM1fKGm@centurylink.net> CAAC (COMMON
Americas Advisory Council) IBM Influencer 2023


On 2/3/2026 8:11 AM, Jim Franz wrote:
> I'm trying to select the value from a log file which holds part of large
> json rows, but the json incomplete and not readable so the "status" never
> in same position. I need the value of status.or even the whole string
> "status":"Paid"
> (scan and substring may be wrong description)
> sample data
> "status":"Paid","allow
> ],"status":"Declined",
>
> I can picture this in RPGLE but trying to improve my sql...
> Found solutions online but not DB2.
>
> Jim Franz