RE: SQL select records with numeric strings

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.