Re: Using SQL, replace all characters less than x'40' in a string with blank

[Dan <dan27649@xxxxxxxxx>]

I can probably get this installed when we upgrade to v7r3, but it won't
happen before then.  Besides, I was able to get your suggestion to work for
me.  I tried this:
values translate('abcdefghijklmnopqrstuvwxyz',
                 '$#', x'9799')
Result: abcdefghijklmno$q#stuvwxyz

So, then I tried this on a test duplicate of the original file:
UPDATE TESTFILE
   SET in_Text = translate( in_Text,
       '                                        ',

x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F')
   where in_Text <> translate( in_Text,
       '                                        ',

x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F')

Note that the file has 7.5 million records, and the in_Text field is 1000
characters in length.  This updated 27,500 rows in about 30 seconds.
Impressive performance.

- Dan

On Tue, Oct 23, 2018 at 11:09 AM Rob Berendt <rob@xxxxxxxxx> wrote:

> I didn't use it in my example, but I believe it is a freebie
> Resource
>    ID     Option  Feature  Description
> 5770SS1   39       5050    International Components for Unicode