Glad to help.
This discussion got me itching to write a UDF for i that emulates as much as
possible the regexp_like function available in Oracle. To that end, I have
written and am now pleased to share that via
http://code.midrange.com/907466d593.html Note that Scott Klement gets
credit for the Regular Expressions header (REGEX_H) that I got from one of
his functions, eons ago.
Simple compile hint and template for function creation are in the source.
Using that, if you wanted to find a string of at least 6 consecutive digits
(for example), you might do something like:
SELECT TEXT_COLUMN
FROM THAT_TABLE
WHERE REGEXP_LIKE(TEXT_COLUMN, '[0-9]{6}') = '1'
Enjoy.
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"The man with the best job in the country is the Vice-President. All he has
to do is get up every morning and say, 'How's the President?'"
-- Will Rogers
Thanks so much - you are correct that the critical parms were reversed
- the to-string must be before the from-string. The fourth parm is a
pad character.
select amtxt,translate(amtxt,'+','0123456789','+')
For my use, it turns out the 4th parm is important. If I don't include,
then the numeric strings reduce to one character; ie "12345" translates
to "+". With the pad it translates to "+++++".
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Dennis Lovelady
Sent: Monday, September 17, 2012 6:41 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL select records with numeric strings
TRANSLATE is available at V5R3, of that I am certain.
Don't you have too many parameters to your TRANSLATE? And aren't the
parms reversed?
select translate(amtxt,'++++++++++','0123456789') looks correct to me.
(But keep in mind, I am the same person who previously sent a REPLACE
where I meant TRANSLATE, with bad formatting. :))
Dennis Lovelady
http://www.linkedin.com/in/dennislovelady
--
"A celebrity is a person who works hard all his life to become well
known, and then wears dark glasses to avoid being recognized."
-- Fred Allen
If you use STRSQL to run this, you should get a message if TRANSLATE
is not supported.
If it is, RTFM - read the "fine" manual, as suggested here - find the
documentation for TRANSLATE and read it carefully - you'll probably
be
able to tell the rest of us.
Personally, I have no idea what is meant by ignored here. But I've
not
paid too close attention to this thread yet.
Regards
Vern
On 9/17/2012 4:46 PM, Stone, Joel wrote:
I am on v5r4. Is TRANSLATE supported on this release??
Any ideas why the TRANSLATE is ignored??
Thanks
select translate (amtxt,'0123456789','++++++++++','+') from mdcmdct
TRANSLATE
WASH FINAL SETTLEMENT BAL TO UNIT TRAIN NUMBER.
000000000000
UNIT: 1234567 B/L DATE: 02/10/03
000000000000
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, September 17, 2012 3:16 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL select records with numeric strings
I like your solution idea.
Any idea why the translate is not working here??
select amtxt,translate(amtxt,'0','+','+') from mdcmdct
A0404 MEMO TEXT
TRANSLATE
WASH FINAL SETTLEMENT BAL TO UNIT TRAIN NUMBER.
000000000000 WASH FINAL SETTLEMENT BAL TO UNIT TRAIN NUMBER.
UNIT: 1541657 B/L DATE: 02/10/03
000000000000 UNIT: 1541657 B/L DATE: 02/10/03
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis
Rodriguez
Sent: Monday, September 17, 2012 12:37 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL select records with numeric strings
Rob,
I believe tha TRANSLATE does allows for this, using the PAD
parameter.
Something like:
SELECT text, TRANSLATE(text, '+', '1234567890', '+') FROM textfile
Would return:
TEXT
TRANSLATE
John Doe's bank account ABA 123456789 John Doe's bank
account ABA
+++++++++
All deliveries to this customer after 1 pm All deliveries to
this
customer after + pm
Sally's bank acct: 777888999 Sally's bank acct:
+++++++++
Wrong number 12345678900 Wrong number
+++++++++++
So if Joel's data is delimited with spaces it would be, as you
wrote, very easy to use the LIKE function. All the same, I concur
that a RPGLE or a SQL function would be a better solution (easier
to
implement and use).
Best Regards,
Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.
_______________________________________________________________________
_
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
_______________________________________________________________________
_
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
