Hi Birgitta
I like your idea, to use TRANSLATE. If he uses this, I think he should add control characters, as well, he is trying to replace X'0D'.
Casting, in my opinion, is not needed - both Glenn Gunderman and I used what was presented and had no problem. As a first attempt I ran this statement in RSS -
values locate(x'40', 'This has a space')
The result was 5.
See you in Fort Worth!
Regards
Vern
On Wed, 1 May, 2024 at 7:36 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:
To: 'midrange systems technical discussion'
I'd try it with TRANSLATE ... something like this:
Update src
set SrcDta = Translate(srcDta,
x'404040404040404040404040404040404040404040404040404040404040404040',
x'202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F41')
With NC;
Also, try to cast the Hex-Values (to CHAR(1)) in your REPLACE statement.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of
Giovanni Arturi via MIDRANGE-L
Sent: Wednesday, 1 May 2024 10:33
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Cc: Giovanni Arturi <giovanni.arturi@xxxxxxxxx<mailto:giovanni.arturi@xxxxxxxxx>>
Subject: Using SQL to replace characters less than Blank
Hi all
I'm looking for an example to replace characters less than Blank (with
blank) ; i found this example at
https://stackoverflow.com/questions/7573321/db2-iseries-sql-clean-up-cr-lf-t
abs-etc :
UPDATE <file>
SET <field> = REPLACE(REPLACE(<field>, x'0D', ' '), x'25', ' ')
WHERE LOCATE(x'25', <field>) > 0
OR LOCATE(x'0D', <field>) > 0
But i receive this error : Message: [SQL0171] Invalid LOCATE function
argument 1. Cause . . . : The specified data type, length, or value of
LOCATE function argument 1 is invalid.
Can some help me ?
Thanks in advance
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx> To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related
questions.
midrange.com
