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

Question

P: n/a
Hi,

I have question:

why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is null
but
returns true on records where foofield is '' (empty string)

regards
Robert
---------------------------(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 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.
On Mon, 13 Oct 2003, Robert Partyka wrote:
why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is
null


Actually, it probably returns unknown(NULL) on such records.
NULL LIKE '%bar%' is unknown, so
NULL NOT LIKE '%bar%' is also unknown.

This is because NULL isn't the same as empty string, nor is it the
absence of a value, but it's an unknown value.

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

http://archives.postgresql.org



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

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
Robert Partyka wrote:
Hi,

I have question:

why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is null
but
returns true on records where foofield is '' (empty string)


SQL specifications.

Empty string and NULL are two different thinks.

Regards
Gaetano Mendola

Nov 12 '05 #3

P: n/a
On Mon, 13 Oct 2003, Robert Partyka wrote:
why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is
null


Actually, it probably returns unknown(NULL) on such records.
NULL LIKE '%bar%' is unknown, so
NULL NOT LIKE '%bar%' is also unknown.

This is because NULL isn't the same as empty string, nor is it the absence
of a value, but it's an unknown value.

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

http://archives.postgresql.org

Nov 12 '05 #4

P: n/a
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.
On Mon, 13 Oct 2003, Robert Partyka wrote:
why such condition:
foofield not like '%bar%'

where foofield is varchar

returns false (or rather even ignore row) on record where foofield is
null


Actually, it probably returns unknown(NULL) on such records.
NULL LIKE '%bar%' is unknown, so
NULL NOT LIKE '%bar%' is also unknown.

This is because NULL isn't the same as empty string, nor is it the
absence of a value, but it's an unknown value.

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

http://archives.postgresql.org



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

http://archives.postgresql.org

Nov 12 '05 #5

P: n/a
vh*****@inreach.com wrote:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.


Really? I don't believe it.

Regards
Gaetano Mendola

Nov 12 '05 #6

P: n/a
Gaetano Mendola wrote:
vh*****@inreach.com wrote:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.


It is insane, but true.

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #7

P: n/a
Mike Mascari <ma*****@mascari.com> writes:
Gaetano Mendola wrote:
vh*****@inreach.com wrote:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.


It is insane, but true.


Uh, yeah, but I think that results in the same behaviour for the case at hand.

ie on oracle this is still holds:

NULL LIKE '%foo%' => NULL

The idiocy is that Oracle does this:

'' LIKE '%foo%' => NULL

because it treats '' as if you had NULL, ie, equivalent to the example above.

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

http://archives.postgresql.org

Nov 12 '05 #8

P: n/a
vh*****@inreach.com wrote:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.


Really? I don't believe it.

Regards
Gaetano Mendola

Nov 12 '05 #9

P: n/a
Gaetano Mendola wrote:
vh*****@inreach.com wrote:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.


It is insane, but true.

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #10

P: n/a
Mike Mascari <ma*****@mascari.com> writes:
Gaetano Mendola wrote:
vh*****@inreach.com wrote:
If you are experienced in Oracle, this might be confusing since Oracle
treats empty string and NULL as being the same.

Really? I don't believe it.


It is insane, but true.


Uh, yeah, but I think that results in the same behaviour for the case at hand.

ie on oracle this is still holds:

NULL LIKE '%foo%' => NULL

The idiocy is that Oracle does this:

'' LIKE '%foo%' => NULL

because it treats '' as if you had NULL, ie, equivalent to the example above.

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

http://archives.postgresql.org

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.