MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2012

RE: SQL select records with numeric strings



fixed

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





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact