How can I tell if a field has spaces or nulls?

[smoothyc]

I have two fields in a table "Item" defined as varchar2(50) and "udc_drp" defined as varchar2(50)

"Item" field contains the following data:
"123456789 .......spaces up to 50"

"udc_drp" field contains the following data:
"Company ........spaces up to 50"

When I do a select on the item field my rows are returned. When I do a select on the udc_drp field, no rows are returned. However if I pad the where clause with spaces for the udc_drp field, to equal 50, my rows are returned.

Why the different? What could be causing the problem?

[chrisrlong]

Something is amiss.

A varchar2 column will not store trailing spaces, even if you enter them. Are you absolutely sure about everything here?

- Chris

[smoothyc]

I'm positive about everything I've written. And I agree, varchar2 does not store spaces. That's why I'm here scratching my head as well.

I do have a theory but I'm not sure how I can check it. I have access to the input text file. What if the file that is being generated is putting something other than spaces in that field? Is there a way to check the hex characters of a text file (other than on the mainframe)?

I see a drop & recreation of the table as well as a recreation of the input file coming. The DBA manager won't like it, but he'll have to get over it. :-)

I'm getting desperate.

[bkanti]

Use the length function to find it

[smoothyc]

Thanks all,

The length function works like a charm. Now I have proof that the input file is incorrect.

(And I'm not crazy :-)

-- Charles