RE: [EXTERNAL] Re: Using SQL TRANSLATE to update multiple character fields in a file

Thanks for your reply Charles
I realize null was NOT what I wanted, but I couldn’t think of another word except delete to describe what I wanted


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


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Wednesday, January 27, 2021 1:28 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] 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

--
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



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.