character field null issue

abhik05 · Post by **abhik05** » Wed Jun 06, 2012 2:40 am

I have one source field 'A' defined as nullable varchar(5).The target database field is defined as nullable char(2). Whenever null values are coming in source field some unknown character is getting inserted in target field.Target database is DB2.
Running ascii(A) function against the field(A) in the database,I am getting value 0,which is null.But when giving clause against the field as 'is null',this specific record is not being selected.

I have given conditions like "if isnull(a) then setnull() else trim(a)" in transformer.But still facing the same.

Any suggestion plz.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 06, 2012 2:44 am

Identify the "unknown character". Perhaps convert it to hex.

abhik05 · Post by **abhik05** » Wed Jun 06, 2012 2:48 am

database is DB2:

hex(field) is giving value 0000.
ascii(field) is giving value 0

Plz suggest.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 06, 2012 2:54 pm

The value is represented by Char(0) or UniChar(0). You can use either of these representations in functions such as Convert(). In particular, this value is not the same as NULL (even though it's referred to in ASCII as "NUL").