Post navigation

Netezza / Puredata – How to replace or trim CHAR(0) is NULL characters in a field

Occasionally, one runs into the problem of hidden field values breaking join criteria. I have had to clean up bad archive and conversion data with hidden characters serval times over the last couple of weeks, so, I thought I might as well capture this note for future use.

I tried the Replace command which is prevalent for Netezza answers to this issue on the web, but my client’s version does not support that command. So, I needed to use the Translate command instead to accomplish it. It took a couple of searches of the usual bad actors to find the character causing the issue, which on this day was chr(0). Here is a quick mockup of the command I used to solve this issue.