Re: Dealing with NUL (hex code 00) characters in files/tables

[CRPence <CRPbottle@xxxxxxxxx>]

  The recommendation to use the IS NULL predicate confuses the database 
NULL value and the x'00' as the NUL [Control] character.  If a column 
for a row has the NUL character as data, then the value of that column 
for that row has been assigned and thus is not the NULL value; i.e. the 
NULL value means no value has been assigned, whether [defaulted] 
implicitly or [set] explicitly.

  There is nothing inherently wrong with control characters in CCSID 
tagged [i.e. not *HEX, not shifted Hex, not CCSID 65536, and not FOR BIT 
DATA] CHAR data, and the database does not diagnose them as a problem 
irrespective of the means to create the database file, DDS or SQL DDL.

  create table qtemp/tablename (fieldname char(50) CCSID 37)
  ;
  insert into  qtemp/tablename (fieldname) values
   ('some text with a x''00'' character suffix' || x'00')
  ,('a line of text without any x''00'' character')
  ,('an embedded 0x00 char before the period' || x'00' || '.')
  ; -- Neither INSERT nor a SELECT of the data gives an error

  If some character(s) should not exist in the CHAR column data, a 
CHECK CONSTRAINT could be added to prevent those characters from being 
included.

Regards, Chuck

On 29 Jun 2012 12:19, Matt Olson wrote:
> Didn't work. There is actually other data in the field. It's just
> that one character is a NUL.
>
> I'm thinking that SQL isn't designed to look for this type of stuff
> as Alan Campin pointed out that SQL (DDL) described tables would have
> never allowed this bad data to be entered in the first place. This is
> only a problem with DDS described tables.
>
> Adding this as yet another reason to kick off our database
> modernization project! Burned by DDS again!
>
> AJordan@xxxxxxxxxxxxxx on Friday, June 29, 2012 12:16 PM wrote:
> > Try
> >
> > Select * from tablename where fieldname is null