Re: the difference between NULL and ' ':an interseting result

> Here's another case - not an experiment, but a situation I discovered in a
> database I'm building. I have a field defined as char(2). I was doing
> count/group by against this field and found a large number that had null
> values in the field - determined by "select count where field is NULL". But
> there was another group that showed on the report as having {nothing} in
> the field - 27379 observations, to be exact. I tried "select count where
> field = ' '" - 0 observations. I tried "select count where field = ' '" -
> 0 observations. Then I tried "select count where field like ' %'" - you
> guessed it, 27379 observations. So it matched on {space}-something, but
> not {space} and not {space-space}. So - what's in that field? It's not
> crucial to the application, but I'm really baffled.
>
>
> Harry Boswell u4imcehb_at_apollo.wes.army.mil
> Computer Scientist
> Information Technology Laboratory
> U.S. Army Corps of Engineers
>
> "select std_disclaimer from opinions_not_shared_by_employer"
Try:

select ascii(substr(field,1,1)) char1,

       ascii(substr(field,2,1)) char2
  from table;

This should give you the collating sequence value of the character. I think that if you are on an EBCDIC system this will not work.

Post the answer - I'm curious.

Matteo diTommaso                       |
ditommm_at_aa.wl.com                      |
(313) 996-7148                         |   This space for rent.
The usual disclaimers apply.           |