Обсуждение: 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

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

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