Preventing the return of NULL values in Oracle, SQL Server and MySQL queries

Sometimes, when writing a query, you don’t want to return a NULL value. This is particularly important during concatenation where the presence of a NULL in one or more of the fields to be joined may result in a NULL being returned (depending on the database being used).
For example, in MySQL the simple, but contrived, query:

  select Concat(firstname, NULL) as mycol from mytable

will always return NULL because one of the values being concatenated with the Concat function is NULL.
Similarly, say we’d like to produce a report using this query:

  select firstname, lastname from mytable

but wherever the firstname is NULL we’d like to replace it with the text ‘*****’.
This is very easy to achieve and the approach is very similar in MS SQL Server, Oracle and MySQL.

To detect that firstname is null in MS SQL Server we use the obviously named IsNull function:

  select IsNull(firstname,'*****'), lastname from mytable

When this query is run, the IsNull function will evaluate firstname. If it is NULL then the replacement value (‘*****’ in this case) will be returned, otherwise the contents of firstname will be returned.

MySQL is almost the same, except the function we need is named IfNull:

  select IfNull(firstname,'*****'), lastname from mytable

In Oracle the syntax is similar but the function is the more obscurely named NVL(Null VaLue?):

  select NVL(firstname,'*****'), lastname from mytable

Three different databases, three differently named functions, one consistent result – a NULL converted into a more usable result.

 

More:

One thought on “Preventing the return of NULL values in Oracle, SQL Server and MySQL queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s