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

From: Ernest Boswell <u4imcehb_at_apollo.wes.army.mil>
Date: 5 Jan 1994 19:38:13 GMT
Message-ID: <2gf4v5$r13_at_Joanna.Wes.Army.Mil>

ditommm_at_aa.wl.com wrote:

: > 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. |

Worked like a charm - i had run the ascii function against the first character, but it didn't occur to me to run it against the second character.

BTW - the field contained {space}-{carriage-return}

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" Received on Wed Jan 05 1994 - 20:38:13 CET