Thread: replace NULL

Hello

i want to run a select query on a table. eg:

select age from people;

if age is NULL then I want to replace it by some text,
say "SHY" so the output is like

name age
me   10
jane SHY
jack 32

how do i make this replacement of null value to some text

__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/

On Tuesday 09 April 2002 14:00, Kancha . wrote:
> Hello
>
> i want to run a select query on a table. eg:
>
> select age from people;
>
> if age is NULL then I want to replace it by some text,
> say "SHY" so the output is like
>
> name age
> me   10
> jane SHY
> jack 32
>
> how do i make this replacement of null value to some text
>

select name, COALESCE(age, 'SHY') from people;

--
Andreas Joseph Krogh (Senior Software Developer) <andreak@officenet.no>
A hen is an egg's way of making another egg.

"Kancha ." <kancha2np@yahoo.com> wrote:
> Hello
> 
> i want to run a select query on a table. eg:
> 
> select age from people;
> 
> if age is NULL then I want to replace it by some text,
> say "SHY" so the output is like
> 
> name age
> me   10
> jane SHY
> jack 32
> 
> how do i make this replacement of null value to some text

UPDATE people
SET age = 'SHY'
WHERE age IS NULL;


I hope that the field age is a text field;


Ciao
Gaetano

-- 
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");

Hi,
>> if age is NULL then I want to replace it by some text,
>> say "SHY" so the output is like
>>
>> name age
>> me   10
>> jane SHY
>> jack 32

If you want to change the presentation but not the actual data you can use:

SELECT name, CASE    WHEN age IS NULL   THEN 'SHY'    ELSE age    END AS age
FROM people


I think there is a function called coalesce (sp?) that does roughly the same
as the CASE-clause above.



Best Regards

Torbjörn "Toby" Andersson
---------------------------------------------------
Embryo Communication      phone: +46 (0) 31-774 39 11(00)
Kungsgatan 7a             fax: +46 (0)31 774 07 80
S-411 19 Gothenburg         mobile: 0708-30 70 04
Sweden                    home: http://www.embryo.se/       mail: torbjorn.andersson@embryo.se
---------------------------------------------------
"A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts,
build a wall, set a bone, comfort the dying, take orders, give orders,
cooperate, act alone, solve equations, analyze a new problem, pitch manure,
program a computer, cook a tasty meal, fight efficiently, die gallantly.
Specialization is for insects"