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
Best Answer
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.