Getting rid of nuls

[blabes@gmail.com]

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(colna me, 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.

[Tonkuma]

Re: Getting rid of nuls

SELECT REPLACE(c, x'00', 'X') from test;
I think that REPACE has undocumented restriction or bug which
terminates evaluation of string1 at x'00'.
If no actual replacement occured, it will return a string truncated at
x'00'.
SELECT LENGTH(REPLACE( c, 'z', 'X')) from test;
-----------------------------------------------------------------

1
-----------
3

1 record(s) selected.

The schema of REPLACE is SYSFUN.
I feel that it is more likely to contain potential problems in SYSFUN
functions than in SYSIBM functions.

SELECT TRANSLATE(colna me, x'00', 'X', 'X') from test;

Change second and third parameters.
SELECT TRANSLATE(c, 'X', x'00') from test;
----------------------------------------------------------------

1
----------
abcXdef

1 record(s) selected.