Replace null values
Hi,
In my query, some rows have null values (length 0).
I wish to replace them with some constant.
I think I am wrong somewhere in this query using coalesce():
select coalesce(u.name, 'anon'), nodecount from users u, ( select n.uid
userid, count(n.nid) nodecount from node n group by n.uid order by n.uid
) t1 where u.uid = t1.userid order by nodecount;
The output is same as that of without coalesce.
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
Nilesh Govindarajan wrote:
Hi,
In my query, some rows have null values (length 0).
a NULL value is not length 0, NULL is not the empty string, rather, NULL
is no value at all.
if you want to change a 0 length string to something, use a CASE or
something.
select CASE WHEN u.name = '' THEN 'anon' ELSE u.name, ....
On 03/23/2010 10:07 AM, John R Pierce wrote:
Nilesh Govindarajan wrote:
Hi,
In my query, some rows have null values (length 0).
a NULL value is not length 0, NULL is not the empty string, rather, NULL
is no value at all.if you want to change a 0 length string to something, use a CASE or
something.select CASE WHEN u.name = '' THEN 'anon' ELSE u.name, ....
Thanks a lot ! It worked :)
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
On 03/23/2010 09:47 AM, Osvaldo Kussama wrote:
2010/3/23 Nilesh Govindarajan<lists@itech7.com>:
Hi,
In my query, some rows have null values (length 0).
NULL or a zero lenght string?
I wish to replace them with some constant.
I think I am wrong somewhere in this query using coalesce():
select coalesce(u.name, 'anon'), nodecount from users u, ( select n.uid
userid, count(n.nid) nodecount from node n group by n.uid order by n.uid )
t1 where u.uid = t1.userid order by nodecount;The output is same as that of without coalesce.
bdteste=# SELECT coalesce(NULL, 'anon'), coalesce('', 'anon');
coalesce | coalesce
----------+----------
anon |
(1 registro)Osvaldo
It is a zero length string. Somebody on the list suggested to use CASE.
It worked. Thanks anyways.
Got to learn about coalesce that it replaces null values and not zero
length strings.
How to convert zero length string to null ?
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
Import Notes
Reply to msg id not found: 690707f61003222117j363b5df9t72e3a9379d92be4a@mail.gmail.com
How to convert zero length string to null ?
UPDATE yourtable SET name=NULL WHERE name='';
On 03/23/2010 11:47 AM, John R Pierce wrote:
How to convert zero length string to null ?
UPDATE yourtable SET name=NULL WHERE name='';
No I don't want to replace it in the table. Just in the query result.
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
On Tue, Mar 23, 2010 at 5:33 PM, Nilesh Govindarajan <lists@itech7.com> wrote:
On 03/23/2010 11:47 AM, John R Pierce wrote:
How to convert zero length string to null ?
UPDATE yourtable SET name=NULL WHERE name='';
No I don't want to replace it in the table. Just in the query result.
use a CASE construct.
select case name='' then null else name end from table where ......
Ian
On 03/23/2010 12:17 PM, Sreelatha G wrote:
Hi,
select case when name='' then null end from table;
Thanks
Sreelatha
On Tue, Mar 23, 2010 at 12:03 PM, Nilesh Govindarajan <lists@itech7.com
<mailto:lists@itech7.com>> wrote:On 03/23/2010 11:47 AM, John R Pierce wrote:
How to convert zero length string to null ?
UPDATE yourtable SET name=NULL WHERE name='';
No I don't want to replace it in the table. Just in the query result.
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com <http://www.itech7.com/>--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Thanks !
--
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com
Import Notes
Reply to msg id not found: f40ed141003222347h64d54e9fnb0f18bfe90c2bdc@mail.gmail.com