× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. September 2012

RE: SQL select records with numeric strings

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.