Here’s a simple trick you can play on your friends. Ask them to pick a number from 1 to 10, and tell them you’ll read their mind and magically extract from it the number they’ve chosen. Don’t give them too long to choose; then put on your best imitation of a magician and say…

7.

I don’t know why, but it’s always 7. Ask people to pick a random number less than 10, and they almost always say 7. I can’t explain it, but feel free to use it to freak your friends out.

Now, here’s how you can freak your Oracle developer friends out. Tell them you can guess the functions they use the most in their code: it’ll be one of the following: count, to_char, to_date, round or nvl.

OK, so maybe that’s not as mind-bending a trick, but the point I’m trying to make is that even though SQL comes equipped with well over a hundred standard functions we all fall back on the same small set that we’re used to, sometimes making them jump through impossible hoops just because we’re not aware of a less popular function designed specifically to meet that need.

I’ve had my nose deep in the documentation, and here are 7 functions of whose existence I’d like to remind you.

NVL2

Ah, NVL2 – like the Hollywood sequel that is inevitably less awesome than the original movie, or the geeky younger sibling of the most popular kid in school, NVL2 has never had the currency that the ubiquitous NVL has. But don’t dismiss it out of hand.

Whereas NVL allows you determine the value to be returned if your parameterised expression is null and returns the value of that expression if it is not null, NVL2 also gives you control of exactly what happens when the expression is not null.

NVL2 Syntax (source: docs.oracle.com)

NVL2 evaluates expr1 and returns expr2 if it is NOT null, but returns expr3 if it is.

It is the equivalent of the following DECODE statement:

And if you’re wondering when you might use this, here’s an example using the EMP table.

Some employees earn a commission on top of their salaries, while others have to make do with just their salary. If we wanted to produce a report on employees’ total pay (i.e, salary plus commission, if commission is not null) we might say:

NULLIF

NULLIF has a different trick up its sleeve. What it does is compare two values that it is fed and if they are equal it returns a null, otherwise it returns the first parameter. 

NULLIF Syntax (source: docs.oracle.com)

Basically, what I’m saying is that if expr1 and expr2 are equal, NULLIF will return a null, and if they’re not it’ll return expr1. That is the equivalent of the following case statement.

And if you’re trying to think up a situation where this might be useful, here’s one. Imagine that in addition to your paid staff your company also retains a number of unpaid interns. The EMP table has a salary of 0 against their names, but you’d rather output a null when you query the table.

COALESCE

Erm, yes, we’re still talking about nulls, about ‘nothing’. Who’d have thought we could spend all this time together chatting about nothing. (Yes, I know that technically, nulls aren’t ‘nothing’, they’re ‘undefined’. But I don’t know any jokes about ‘undefined’, so leave me alone.)

Back to coalesce. The coalesce function accepts 2 or more parameters and will return the first one that is not null. 

What that means is that:

will return expr1 if is not null, but will return expr2 if it is and expr2 is not. However, if both expr1 and expr2 are null, it will return expr3 if it is not undefined. And so on. This is the equivalent of the following case statement:

In other words, coalesce can accomplish in one line something that, with a case statement, has the potential to get as long-winded and boring as listening to your ex talk about how perfect their new lover is.

LNNVL

Before we move on from nulls, I’ve got one more: LNNVL.

It’s a bit of an odd one; it’s like that game Opposites, that young children enjoy playing. It accepts an expression as a parameter and, if that expression evaluates as true, it’ll return false; if, however, the expression evaluates as false or unknown, it’ll return true. 

As LNNVL returns a Boolean, it can only be used in where clauses or in the when conditions of a case statement. 

However, more than how you’re probably wondering why – and when –  you’d ever want to use LNNVL. Here’s an example. Remember that company you’re running that has both paid staff and unpaid volunteers? Say you wanted to query the sal column of the Emp table to find all members of the team who earn less than £10,000. The following query will return all paid staff who earn less than that amount:

But one way to include unpaid volunteers in the resultset (beyond the obvious solution of wrapping an NVL around the sal in the where clause) is to use LNNVL as follows:

This works because LNNVL returns true when the condition it is fed is false (i.e salary is less than 10000) or unknown (i.e salary is null). 

Oh, and if you were wondering what on earth LNNVL stands for, it is: logical not null value. I bet you’re sorry you asked.

Compose

We’re moving on from functions concerning nulls, so compose yourself.

The compose function accepts a string as a parameter – CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB - and translates it into a Unicode string in its fully normalized form in the same character set as the input.  Unicode, as you probably already know, is the standard that allows for consistent representation of text from the various writing systems. What this then means is that if you’re like me and were brought up with only 26 boring characters in your alphabet, compose allows you access all those exotic characters that other peoples use.  Wanna go to Germany and order a Big Mac – ein Big Mäc –  for a Nigerian named Sola – Sọlá? Alright, big spender.  First you’ll need to know what the code point is for your particular diaeresis and then combine it – compose it – with the right letter. For example, the code point for the double dots – the umlaut – is 0308, so if you wanted to order that burger it’ll be:

And the code point for the dot that goes under letters is 0323, while the accent is 0301 (no, I haven’t got them all memorised; I’m using this chart). So that Nigerian name is: 

Basically, what compose does is introduce the ordinary character – the ‘a’, for instance – to  the combining diaeresis – the ̈, for instance – and encourage them to have a baby together: the ä.

UNISTR

Actually, I’ve done this backwards.  You might have noticed the call to UNISTR in the examples above.  The UNISTR function accepts character data and returns it in the national character set. You can feed it a Unicode encoding value – in the form of  ‘\nnnn‘ where ‘nnnn’ is the hexadecimal value of a character in UCS-2 encoding format.  Here’s an example of what I mean: 

You can combine ASCII strings with Unicode encoding values in your calls to unistr. So, returning to the examples we used when talking about compose, you could run the following:

This is because the Unicode encoding value for an a with an umlaut is \ooe4. Here’s a list if you’re looking for other characters. 

In my current job, I help manage a database that sits beneath a public-facing Apex website, and occasionally we are tripped up by non-Latin characters. Unistr – and to a lesser extent, compose – are my secret weapons in those instances.

DECOMPOSE

I might as well mention decompose too, cos it hangs out with compose and unistr, not because I find myself using it much. 

Decompose, unsurprisingly, is the opposite of compose.  You know how compose will take, for instance, an a and the umlaut code point and – abracadabra – return an a-umlaut.  Well, decompose will take an a-umlaut, or any such character, and return an a followed by the two dots of an umlaut. 

Conclusion

These 7 functions won’t change your life. Sorry about that. But what they might do is help get you out of a bind, or help save you a few lines of convoluted code.  And that’s not nothing.

Now, close your eyes and think of a number from 1 to 10.