Re: Using SQL to replace characters less than Blank

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

I don't know what is the reason for the error you see - I copied your 
UPDATE statement, using an actual table and column - no error.

I wonder if an apostrophe is not the simple one it seems. Maybe the copy 
from stackoverflow had the Windows-style curly apostrophe - I'm only 
guessing, but one difference - I assume you are using CCSID for Italy - 
is the CCSID. But that should result in a different error.

What is the version of your IBM i? I ran the example on a 7.5 machine

Cheers
Vern

On 5/1/2024 3:33 AM, Giovanni Arturi via MIDRANGE-L wrote:
> 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