isnull sql function

klycette

This is more sql related rather than php, but I'm wondering if someone knows of a function similar to isnull in sql. I know that I've used one in the past that placed a specified default string into a field if that field was left empty by the user. This is esentially was isnull does, but I'm pretty sure it was another function. Does anyone know?

OhLordy

You could alter the table to put a default value into the column.

The comparison function you're looking for is IS NULL have a look here for
comparison functions

HTH
Rob

Sxooter

I'm not sure what you're asking. Doesn't the SQL syntax IS NULL work?

select case when field IS NULL then 0 else field end from test;

select * from test where field is not null;

etc???

klycette

That's not quite what I meant. The function ISNULL can enter a default string into a field if the user doesn't enter anything so you don't get nulls. For example,

ISNULL(fieldname, 'Not Supplied')

will enter "Not Supplied" into the field if nothing else is enter, so a query won't just bring back nothing. I think this will solve my problem, but I'm just trying to remember if there's another SQL function that does the same thing...I think there is, but I can't remember it.

Sxooter

sounds a bit like coalesce:

select coalesce (fieldname,'that things null') from some_table;

P.s. my example with the case statement does exactly that. If it was a text field you could do:

select case when field IS NULL then 'that field is apparently null' else field end from test;

klycette

You can use either of these functions when you're inserting information as well, correct? I guess it's six of one, half a dozen of the other where you do it.

Sxooter

When you're inserting? If you write a trigger function I guess you could. Or you could use a subselect during insert probably.

Why not just use a default type or the DEFAULT keyword:

create table test (name text default 'stan');
insert into test values (DEFAULT);

This for sure works (in postgresql.):

insert into test values ((select coalesce('yourval','default')));