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 
  • 1
    Given that the question is tagged with db2-zos, I presume your character field uses the EBCDIC encoding, in which 0x3F is also a control character, not "?". – mustaccio Jul 18 '18 at 10:55
  • @mustaccio Yes it is EBCDIC. But why is the translate function replacing 0x3F if it is not in the input translation table? – user3193317 Jul 18 '18 at 11:12
  • 2
    Those HEX values are ASCII. Can you use the db2-zos HEX() SQL function on the result of the TRANSLATE to help you work out what is going on. I.e. to eliminate any conversion your GUI environment is doing itself – Paul Vernon Jul 18 '18 at 11:50
  • BTW I assume you mean (HEX \x00 until \x1F) not (HEX \x00 until \x7F) – Paul Vernon Jul 18 '18 at 11:52
  • @P.Vernon thanks for the hint, HEX(TRANSLATE(...)) is showing me 92948893929440, and HEX(field) is showing me 92948893929415. Still don't quite understand it yet. 15 = New Line, maybe there is some problem with translating "New Line"? – user3193317 Jul 18 '18 at 12:26

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.

  • Agreed. They to change 'X' to 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' (of at least the right length) – Paul Vernon Jul 18 '18 at 13:12
  • 1
    Or TRANSLATE( input, 'X', X'0102....1F', 'X'). This defines 'X' instead ot blank as the pad character. This way the length of the "from-string" is automatically matched. – phunsoft Jul 18 '18 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.