I don\u2019t know why, but it\u2019s always 7. Ask people to pick a random number less than 10, and they almost always say 7. I can\u2019t explain it, but feel free to use it to freak your friends out.<\/p>\n
Now, here\u2019s how you can freak your Oracle developer friends out. Tell them you can guess the functions they use the most in their code: it\u2019ll be one of the following: count, to_char, to_date, round or nvl.<\/p>\n
OK, so maybe that\u2019s not as mind-bending a trick, but the point I\u2019m 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\u2019re used to, sometimes making them jump through impossible hoops just because we\u2019re not aware of a less popular function designed specifically to meet that need.<\/p>\n
I\u2019ve had my nose deep in the documentation<\/a>, and here are 7\u00a0functions of whose existence I\u2019d like to remind you.<\/p>\n Ah, Whereas NVL2 Syntax (source: docs.oracle.com<\/a>)<\/p>\n It is the equivalent of the following DECODE statement:<\/p>\n And if you’re wondering when you might use this, here’s an example using the EMP table.<\/p>\n 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:<\/p>\n NULLIF Syntax (source: docs.oracle.com<\/a>)<\/p>\n Basically, what I’m saying is that if\u00a0expr1<\/em> and\u00a0expr2<\/em> are equal, 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.<\/p>\n 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.)<\/p>\n Back to What that means is that:<\/p>\n will return\u00a0expr1<\/em> if is not null, but will return\u00a0expr2<\/em> if it is and\u00a0expr2<\/em> is not. However, if both\u00a0expr1<\/em> and\u00a0expr2<\/em> are null, it will return\u00a0expr3<\/em> if it is not undefined. And so on. This is the equivalent of the following case statement:<\/p>\n In other words, Before we move on from nulls, I’ve got one more: 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\u00a0true<\/em>, it’ll return\u00a0false;<\/em> if, however, the expression evaluates as\u00a0false<\/em> or unknown, it’ll return\u00a0true.<\/em>\u00a0<\/p>\n As However, more than\u00a0how<\/em> you’re probably wondering\u00a0why – <\/em>and when –\u00a0<\/em> you’d ever want to use But one way to include unpaid volunteers in the resultset (beyond the obvious solution of wrapping an NVL around the\u00a0sal<\/em>\u00a0in the where clause) is to use This works because Oh, and if you were wondering what on earth We’re moving on from functions concerning nulls, so compose yourself.<\/p>\n The NVL2<\/h5>\n
NVL2<\/code> – 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<\/code> has never had the currency that the ubiquitous NVL<\/code> has. But don\u2019t dismiss it out of hand.<\/p>\nNVL<\/code> 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<\/code> also gives you control of exactly what happens when the expression is not null.<\/p>\n![\"NVL2\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2017\/01\/nvl2.gif\")
<\/p>\nNVL2<\/code> evaluates\u00a0expr1<\/em> and returns\u00a0expr2<\/em> if it is NOT null, but returns\u00a0expr3<\/em> if it is.<\/p>\nDECODE(expr1, NULL, expr2, expr3)<\/pre>\n
SELECT empno,\u00a0NVL2(comm,sal+comm,sal) \"Total Pay\"\r\nFROM emp;<\/pre>\n
NULLIF<\/h5>\n
NULLIF<\/code> 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.\u00a0<\/p>\n![\"NULLIF\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2017\/01\/nullif.gif\" "\\"\\"")
<\/p>\nNULLIF<\/code> will return a null, and if they’re not it’ll return\u00a0expr1.<\/em> That is the equivalent of the following case statement.<\/p>\nCASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END<\/pre>\n
SELECT empno, NULLIF(sal,0) \"Salary\"\r\nFROM emp;<\/pre>\n
COALESCE<\/h5>\n
coalesce<\/code>. The coalesce<\/code> function accepts 2 or more parameters and will return the first one that is not null.\u00a0<\/p>\nCOALESCE(expr1, expr2, expr3)<\/pre>\n
CASE\r\n\u00a0 \u00a0WHEN expr1 IS NOT NULL THEN expr1\r\n\u00a0 \u00a0WHEN expr2 IS NOT NULL THEN expr2\r\n\u00a0 \u00a0ELSE expr3\r\nEND<\/pre>\n
coalesce<\/code> 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.<\/p>\nLNNVL<\/h5>\n
LNNVL<\/code>.<\/p>\nLNNVL<\/code> returns a Boolean, it can only be used in where clauses or in the when conditions of a case statement.\u00a0<\/p>\nLNNVL<\/code>. Here’s an example. Remember that company you’re running that has both paid staff and unpaid volunteers? Say you wanted to query the\u00a0sal<\/em>\u00a0column of the\u00a0Emp<\/em>\u00a0table to find all members of the team who earn less than \u00a310,000. The following query will return all\u00a0paid<\/em> staff who earn less than that amount:<\/p>\nSELECT empno\r\nFROM emp\r\nWHERE sal <10000;<\/pre>\n
LNNVL<\/code> as follows:<\/p>\nSELECT emp_id\r\nFROM employees\r\nWHERE LNNVL(salary >= 10000);<\/pre>\n
LNNVL<\/code> returns\u00a0true<\/em> when the condition it is fed is\u00a0false<\/em> (i.e salary is less than 10000) or unknown (i.e salary is null).\u00a0<\/p>\nLNNVL<\/code> stands for, it is: logical not null value<\/em>. I bet you’re sorry you asked.<\/p>\nCompose<\/h5>\n
compose<\/code> function accepts a string as a parameter – CHAR<\/code>, VARCHAR2<\/code>, NCHAR<\/code>, NVARCHAR2<\/code>, CLOB<\/code>, or NCLOB -\u00a0<\/code>and translates it into\u00a0a Unicode string in its fully normalized form in the same character set as the input. \u00a0Unicode, 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<\/code> allows you access all those exotic characters that other peoples use. \u00a0Wanna go to Germany and order a Big Mac – ein<\/em>\u00a0Big M\u00e4c –\u00a0<\/i> for a Nigerian named Sola – S\u1ecdl\u00e1<\/em>? Alright, big spender. \u00a0First you’ll need to know what the code point is for your particular diaeresis<\/a>\u00a0and then combine it – compose<\/em> it –\u00a0with 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:<\/p>\nSELECT 'Big M'||COMPOSE ( 'a' || UNISTR('\\0308') )||'c' \r\nFROM DUAL;\u00a0<\/pre>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2017\/01\/allthingsoracle_decompose.jpg\" "\\"\\"")
<\/p>\n