Null vs Empty

[mittit]

I have got a problem handling some records which have a couple of columns (A, B, C) with Null value representing with the ? sign. Also, those records have another column (D) which is totally empty. I want to exclude those records with empty D column but don't know what criteria to put in to WHERE. Given that space (WHERE D = ' ' or '') does not work

 

[BillDHS]

Try where ColumnD IS NULL

 

[tdatgod]

What is the Data type for Column D?

is it defined as NULLABLE?

is it a CHAR vs VARCHAR.

execute a

show table yout_table_name;


Again there is a difference between

ColumnD is NULL

and

ColumnD = '' (you must use single quotes. )

and

trim(ColumnD) = ''

the first one means the column is NULLABLE and contains is set to NULL.

The second means the Column actually contains data but that data is a VarChar and it all blanks.

And the third is for CHAR fields that contains all blanks. an empty CHAR field that isn't set explicitly to NULL is filled with spaces.


you may need to combine them like...

where ColumnD is NULL
or
trim(ColumnD) = '';

or

where ColumnD is NOT NULL
and
trim(ColumnD) <> '';

 

[mittit]

Thanks for the inputs
As there is no space in the column D, so trim does not work, neither does (D IS NULL)

It's very strange, I don't understand why that sort of data could be entered into the table

Here is the column info
Type: CV
Nullable:Y
Format: X(255)
MaxLength: 255
UpperCase: N
CharType: 1

 

[BillDHS]

I wonder if it is an unprintable character(?) try from the other direction something like:

WHERE colD > ' ' (space)
or
WHERE NOT colD < ' ' (space)

 

[tdatgod]

maybe ColD really is a '?' and not a '?' posing as a NULL?

what does

sel ColA, ColB, ColC, ColD, character_length(ColD)
from mytable ;

how about dumping it in HEX

sel ColA, ColB, ColC, ColD, CHAR2HEXINT(ColD)
from mytable ;