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.
Example Select Statement
Here is a quick example select SQL to identify problem rows.
|SELECT TRANSLATE(F.BLOGTYPE_CODE, CHR(0), ”) AS BLOGTYPE_CODE, BT.BLOG_TYP_ID, LENGTH(BT.BLOG_TYP_ID) AS LNGTH_BT, LENGTH(F.BLOGTYPE_CODE) AS LNGTH_ BLOGTYPE
FROM BLOGS_TBL F, BLOG_TYPES BT WHERE TRANSLATE(F.BLOGTYPE_CODE, CHR(0), ”) = BT.BLOG_TYP_ID AND LENGTH(BT.BLOG_TYP_ID) <>Length(LENGTH(F.BLOGTYPE_CODE) ;
Example Update Statement
Here is a quick shell update statement to remove the Char(0) characters from the problem field.
|Update <<Your Table Name>> A
Set A.<<Your Field Name>> = TRANSLATE(A.<<Your FieldName>>, CHR(0), ”)
where length(A.<<Your Field Name>>) <> Length(A.<<Your FieldName>>) And << Additional criteria>>;