RE: SQL select records with numeric strings

["Dennis Lovelady" <iseries@xxxxxxxxxxxx>]

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.