On Thu, 11 Sep 2003, [ISO-8859-1] Alexander Bl�m wrote:
> hello,
>
> I'm facing an odd problem.
> I have a query:
>
> SELECT "K�rzel", "Autor(en)", "Titel"
> FROM "tblTitelangaben"
> WHERE "Titel"||"K�rzel"||"Monographie-K�rzel"||"Autor(en)" ILIKE '%er%';
>
> this works like a charm.
>
> but if I add another ||"something" - which might be emtpy, I get no
> results.. why?
>
> WHERE
> "Titel"||"K�rzel"||"Monographie-K�rzel"||"Autor(en)"||"Herausgeber"
> ILIKE '%er%';
>
> || is the same as "OR", is it not?
No, it's string concatenation. Also, remember that NULLs are special
a || NULL is NULL. You can use coalesce(field,"") to get an empty string
if field is NULL.
> 1 OR 0 = 1
> 1 OR 1 = 1
> 0 OR 0 = 0
> right?
And even for OR, the above isn't true in SQL because of NULLs.
1 | 0 | N
1 1 | 1 | N
0 1 | 0 | N
N N | N | N