Re: empty string or null

Frank van Bortel schrieb am 29.03.2007 in <1175179877.256006.199430_at_n59g2000hsh.googlegroups.com>:

> On 29 mrt, 16:44, "Tim B" <nos..._at_someisp.ca> wrote:

>> Why does this query return null?  I would expect it to return 0. It appears
>> trim() is returning null,
>> select length(trim(substr('           n',1, 3))) from dual

> Because this is Oracle, and Oracle treats empty stings as NULL.
> Basically your length function gets a NULL, because your substr
> returns a string,
> containing just spaces, and trimming spaces leaves nothing.

Because of this behavier it is called varchar2 instead of varchar. You can not change it, so love it ;)

remember this, if you compare to an empty string

SELECT * FROM T
WHERE A=''; will have no results.

SELECT * FROM T
WHERE A=B; will not include rows where A='' AND B=''. You must write

SELECT * FROM T
WHERE (A=B) or ((A is NULL) and (B is NULL));

try a JOIN ...
SELECT * FROM ADDRESSES A
LEFT JOIN JOBS J ON
  (A.FIRSTNAME=B.FIRSTNAME) and (A.MIDDLENAME=B.MIDDLENAME) and   (A.FAMILYNAME=B.FAMILIYNAME); no MIDDLENAME- no job

Andreas Mosmann

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de