308

In MS SQL-Server, I can do:

SELECT ISNULL(Field,'Empty') from Table

But in PostgreSQL I get a syntax error. How do I emulate the ISNULL() functionality ?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
  • 1
    No you can't do that in MSSQL. That code will not compile. `ISNULL` takes two arguments and returns the second is the first is `null`, otherwise the first. – GSerg Feb 06 '10 at 20:40
  • @GSerg, you are right. fixed that. – Byron Whitlock Mar 08 '10 at 19:29
  • Gserg and Byron yes you can see here Example from my PC SELECT isnull( a.FechaEntregada ,'') as test from dbo.Amonestacion a https://msdn.microsoft.com/en-us/library/ms184325.aspx – Juan May 31 '15 at 15:28

4 Answers4

530
SELECT CASE WHEN field IS NULL THEN 'Empty' ELSE field END AS field_alias

Or more idiomatic:

SELECT coalesce(field, 'Empty') AS field_alias
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Kyle Butt
  • 9,340
  • 3
  • 22
  • 15
  • 59
    +1 for `coalesce`. (P.S. You can do that in MS SQL Server, too.) – Alison R. Feb 06 '10 at 20:09
  • 4
    There are other cases for using IS NULL though, so it's good to know both. – Kyle Butt Feb 06 '10 at 20:14
  • 36
    I think it's worth noting that it is `coalesce` that is in SQL standard, with `isnull` being an MS-specific function that essentially is `coalesce` with only two parameters. – GSerg Feb 06 '10 at 20:35
  • 2
    `COALESCE` is ANSI standard, and supported SQL Server 2000+ – OMG Ponies Feb 06 '10 at 23:00
  • 5
    Coalesce() also handles type promotion properly (exactly like UNION SELECT does), while IsNull() does not. – ErikE Feb 07 '10 at 00:03
  • 3
    Beware of doing operations on nullable values inside COALESCE, e.g. `COALESCE(my_table.nullable_column - my_table.cost_price, 0)`, as you may not get the coalesced zero value you expect. – Petrus Theron Jul 19 '16 at 11:43
  • 8
    It is worth pointing out that ISNULL and COALESCE are not the same. IsNull forces the result-type to the type of argument1, while coalesce uses the respective types for each argument. If you just search-and-replace isnull with coalesce, you can potentially get a lot of errors... – Stefan Steiger Feb 20 '19 at 16:02
101

Use COALESCE() instead: 

SELECT COALESCE(Field,'Empty') from Table;

It functions much like ISNULL, although provides more functionality. Coalesce will return the first non null value in the list. Thus:

SELECT COALESCE(null, null, 5);

returns 5, while

SELECT COALESCE(null, 2, 5);

returns 2

Coalesce will take a large number of arguments. There is no documented maximum. I tested it will 100 arguments and it succeeded. This should be plenty for the vast majority of situations.

Richard D
  • 327
  • 3
  • 16
Jim Clouse
  • 8,774
  • 6
  • 32
  • 25
29

How do I emulate the ISNULL() functionality ?

SELECT (Field IS NULL) FROM ...
Satpal
  • 132,252
  • 13
  • 159
  • 168
Artur
  • 501
  • 4
  • 2
  • 5
    This emulates the exact functionality of isnull, not sure why it's downvoted – smackshow Mar 26 '13 at 10:38
  • The best answer for the question, of course. This expression is the full equivalent of ISNULL(). COALESCE() is very smart and interesting to know but it cannot perform an ISNULL() while it is closed. – Skrol29 Apr 02 '14 at 11:26
  • 20
    I don't know what `ISNULL` you commenters are referring to, but `field IS NULL` gives a boolean value, while `ISNULL` in SQL Server operates like `COALESCE`: it returns one of the non-`NULL` values. This answer is terribly wrong. See the documentation: [`ISNULL`](https://msdn.microsoft.com/en-us/library/ms184325.aspx). – jpmc26 Feb 04 '15 at 04:03
  • 14
    I suspect these commenters are MySQL users who didn't realize the question starts with, "In MS SQL Server, ..." MySQL has an ISNULL() function that takes a single argument and returns 0 or 1. T-SQL's version takes two arguments and behaves like COALESCE or Oracle's NVL. – David Noha Jun 08 '16 at 22:15
  • 2
    The answer is wrong. Though it was explained in above comments, some people are still wondering why. Weird... – greatvovan Oct 25 '18 at 01:09
  • 3
    greatvovan, Whether or not this was the intent of the original poster, I believe what people are wanting is the answer to "How do I check if a field is null", not necessarily "How exactly does the ISNULL function work". That was the case with me and this answer is perfect for that. – Freeman Helmuth Mar 01 '19 at 22:21
21

Try:

SELECT COALESCE(NULLIF(field, ''), another_field) FROM table_name
Smern
  • 18,746
  • 21
  • 72
  • 90
user2718914
  • 211
  • 2
  • 2