By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

query-question

P: n/a
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?

1 OR 0 = 1
1 OR 1 = 1
0 OR 0 = 0
right?
therefore I should get the same, or more results...

any hints are greatly appreciated!

TIA,
alex
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Hello

|| isn't OR!!

testdb011=> select 'aaaa'||'bbbbbbb';
?column?
-------------
aaaabbbbbbb

This is SQL, not java or C. OR is OR in SQL :->

Pavel
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?

1 OR 0 = 1
1 OR 1 = 1
0 OR 0 = 0
right?
therefore I should get the same, or more results...

any hints are greatly appreciated!

TIA,
alex
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 11 '05 #2

P: n/a
|| is the string concatenation operator, so what you're doing is
equivalent to using the concatenated values of those fields.
I have no experience with ILIKE, but you might figure out yourself
what's wrong...

HTH,
Csaba.
On Thu, 2003-09-11 at 13:36, 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?

1 OR 0 = 1
1 OR 1 = 1
0 OR 0 = 0
right?
therefore I should get the same, or more results...

any hints are greatly appreciated!

TIA,
alex
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #3

P: n/a
|| is the string concatenation operator, so what you're doing is
equivalent to using the concatenated values of those fields.
I have no experience with ILIKE, but you might figure out yourself
what's wrong...

HTH,
Csaba.
On Thu, 2003-09-11 at 13:36, 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?

1 OR 0 = 1
1 OR 1 = 1
0 OR 0 = 0
right?
therefore I should get the same, or more results...

any hints are greatly appreciated!

TIA,
alex
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 11 '05 #4

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #5

P: n/a
Stephan Szabo wrote:
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


THANK YOU VERY MUCH!!! ;))

that did the deed, exactly as intended!!
now I understand.

thanks again to all of you!

cheers,
alex

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #6

P: n/a

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 11 '05 #7

P: n/a
Stephan Szabo wrote:
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


THANK YOU VERY MUCH!!! ;))

that did the deed, exactly as intended!!
now I understand.

thanks again to all of you!

cheers,
alex

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 11 '05 #8

P: n/a
On Thu, 11 Sep 2003, 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. Any component must not be NULL. Use coalesce("Herausgeber",'') to
ensure it's never NULL.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #9

P: n/a
On Thu, 11 Sep 2003, 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. Any component must not be NULL. Use coalesce("Herausgeber",'') to
ensure it's never NULL.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.