Is there a way to use either the REPLACE() or TRANSLATE() function to
change ascii nul characters (chr(0)) in a given table/column to a
different character? I've tried what I think should work, but both
functions seem to get confused when they encounter the nul in the
column. Here's what I've tried:
CREATE TABLE test(c CHAR(10));
INSERT INTO test VALUES('abc' || x'00' || 'def');
SELECT HEX(c) from test;
SELECT REPLACE(c, x'00', 'X') from test;
SELECT TRANSLATE(colname, x'00', 'X', 'X') from test;
I could write something in sqc or perl, but I'd prefer to stay inside
DB2 (9.1.4 LUW) if possible.