DB2/iSeries SQL clean up CR/LF, tabs etc

Question

I need to find and clean up line breaks, carriage returns, tabs and "SUB"-characters in a set of 400k+ string records, but this DB2 environment is taking a toll on me.

Thought I could do some search and replacing with the REPLACE() and CHR() functions, but it seems CHR() is not available on this system (Error: CHR in *LIBL type *N not found). Working with \t, \r, \n etc doesn't seem to be working either. The chars can be in the middle of strings or at the end of them.

DBMS = DB2
System = iSeries
Language = SQL
Encoding = Not sure, possibly EBCDIC

Any hints on what I can do with this?

Answer 1

I used this SQL to find x'25' and x'0D':

SELECT 
     <field>
    , LOCATE(x'0D', <field>) AS "0D" 
    , LOCATE(x'25', <field>) AS "25" 
    , length(trim(<field>)) AS "Length"
FROM <file> 
WHERE   LOCATE(x'25', <field>) > 0 
    OR  LOCATE(x'0D', <field>) > 0 

And I used this SQL to replace them:

UPDATE <file> 
SET <field> = REPLACE(REPLACE(<field>, x'0D', ' '), x'25', ' ')
WHERE   LOCATE(x'25', <field>) > 0 
    OR  LOCATE(x'0D', <field>) > 0 

Answer 2

If you want to clear up specific characters like carriage return (EBCDIC x'0d') and line feed (EBCDIC x'25') you should find the translated character in EBCDIC then use the TRANSLATE() function to replace them with space.

If you just want to remove undisplayable characters then look for anything under x'40'.