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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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