Обсуждение: lower() not working correctly...?

I have the following query:

select lower(firstname) || ' ' || lower(lastname) from person

firstname and lastname are VARCHAR

lower() returns NULL when firstname OR lastname is NULL, is this correct?

This is 8.2devel from 24.08.2006.

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Karenslyst Allé 11      | know how to do a thing and to watch         |
PO. Box 529 Skøyen      | somebody else doing it wrong, without       |
0214 Oslo               | comment.                                    |
NORWAY                  |                                             |
Mobile: +47 909  56 963 |                                             |
------------------------+---------------------------------------------+

am  Fri, dem 15.09.2006, um 10:06:40 +0200 mailte Andreas Joseph Krogh folgendes:
> I have the following query:
> 
> select lower(firstname) || ' ' || lower(lastname) from person
> 
> firstname and lastname are VARCHAR
> 
> lower() returns NULL when firstname OR lastname is NULL, is this correct?

Yes. You can user coalesce for this:

select coalesce(lower(firstname),'') || ...



HTH, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Andreas Joseph Krogh a ecrit le 15/09/2006 10:06:
> I have the following query:
> 
> select lower(firstname) || ' ' || lower(lastname) from person
> 
> firstname and lastname are VARCHAR
> 
> lower() returns NULL when firstname OR lastname is NULL, is this correct?
> 
> This is 8.2devel from 24.08.2006.
> 

NULL is an unkown value. lower of an unkown value is unknown... thus 
NULL. This is correct behavior.


-- 
Guillaume.

unsubscribe