DB2: Why does translate function also replace spaces? I would like to remove control characters (HEX \x00 until \x7F) from a DB2 data base field. For this I tried to apply the following translate-function:

TRANSLATE(field, 'X',  x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F')


It does work for control chracters, but the function also translates question marks, and I don't understand why? Interestingly question marks are not replaced by "X" like the control characters but by a space \x20. 

Why does it behave like that?

Original values:

Text View: mhlkm
Hex: 6D 68 6C 6B 6D 3F          
Text View in Hex Viewer: mhlkm?


Translated values:

Text View: mhlkm
Hex: 6D 68 6C 6B 6D 20 
Text View in Hex Viewer: mhlkm
The "to-string" in your TRANSLATE is a single "X", so only the first character found in "from-string" (X'00' in your case) will be translated to "X". All other characters found in "from-string" will be replace by the "pad" character. You don't specify one, so a blank is used by default. This is why the X'15' at the end of your inut field is translated to X'40', which is an EBCDIC blank.