Getting rid of nuls
in [DB2]
|
Prev: Cannot redirect restore into a different database name.
Next: Getting memory size for an application
From: blabes on 18 Jun 2008 19:13 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.
From: Tonkuma on 19 Jun 2008 01:50 > 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(colname, 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.
From: blabes on 19 Jun 2008 16:46
On Jun 18, 10:50 pm, Tonkuma <tonk...(a)fiberbit.net> wrote: > > 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(colname, 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. That was it! Thanks for finding my stupid mistake on the parameter order. Once I fixed that, TRANSLATE() worked as advertised! |