Re: Using SQL TRANSLATE to update multiple character fields in a file

Birgitta's answered part of your question. Yes you can update all 6 at
once...

To answer the other part "Can I replace it with null, in other words -
actually, deleting the character"

Short answer is "No, and yes" :)

No you can't replace it with NULL (we you could but that would likely cause
issues)
Yes you can delete it. Simply use the REPLACE() function

update yourtable
set field1 = replace(field1,'|')
, field2 = ....

Charles





On Wed, Jan 27, 2021 at 10:19 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi everyone
We are on V7r3
To cut a LOOOOOONG story short - I need to update a number of fields in
one file to remove the pipe (|) character from each of the character fields
in the file
Can I replace it with null, in other words - actually, deleting the
character, or must I in fact replace it with space?

I believe TRANSLATE is the way to go, because I suspect there may be other
characters that will need to be replaced, as time goes on
Here is the scenario
Lets say the record has 6 character fields in the file (FILE01)
FIELD1
FIELD2
FIELD3
FIELD4
FIELD5
FIELD6

My question is:-
Can I update the file - using ONE sql command, for ALL 6 fields?

As always - all replies gratefully accepted


Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill



The Nature’s Bounty Co. is now The Bountiful Company! Our email address
will be changing from @nbty.com to @bountifulcompany.com. Please update
your email address book so that important communications are not
interrupted.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: 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
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com