Category: Oracle

Oracle CASE Statement

Posted on by Ian

In Oracle Database, the CASE statement compares a list of conditions and returns one of multiple possible expressions.

Oracle Database’s CASE statement is very similar to the CASE expression (which is defined in the SQL standard (ISO/IEC 9075)). However, Oracle supports both the CASE expression and the CASE statement, and there’s a distinction between the two. The CASE statement can be used to execute of a sequence of PL/SQL statements, whereas the CASE expression returns a single value. Also, there’s a difference in how they deal with the lack of an ELSE clause when a condition is not met.

Continue reading →

Oracle CASE Expression

Posted on by Ian

In Oracle Database, the CASE expression compares a list of conditions and returns one of multiple possible expressions. It allows us to use IF … THEN … ELSE logic in SQL statements without having to invoke procedures.

The CASE expression is included in the SQL standard (ISO/IEC 9075), and most major RDBMSs support it.

Oracle also has a CASE statement that’s very similar to the CASE expression, but with some minor differences.

Continue reading →

SQL ISNULL() Explained

Posted on by Ian

Some RDBMSs provide an ISNULL() function that can be used when dealing with potentially null values.

MySQL, MariaDB, and Oracle Database each have an ISNULL() function that returns 1 if its argument is null, and 0 if it’s not.

SQL Server also has an ISNULL() function, but it works differently. It works more like how the IFNULL() function works in some other RDBMSs.

Other RDBMSs, such as PostgreSQL and SQLite don’t include an ISNULL() function, but they do support the IS NULL predicate (as do the other RDBMSs).

Continue reading →

Find All Non-Numeric Values in a Column in SQL

Posted on by Ian

If you ever encounter a character column that should be numeric, there’s always a possibility that it contains non-numeric data that you don’t know about.

For example, someone might have set up a Price column as a varchar column that should have been a numeric column, and now you need to clean up after them. You might start by identifying all non-numeric data so that you can work out what to do with it before converting the column to a numeric type.

In SQL, you can run a query to return non-numeric data from the column. The query you use will largely depend on your DBMS.

Continue reading →