java statement, oracle nvl, LIKE comparison

Question

I'm using Java 6 and Oracle 10. I have a java statement and I want to test a null or empty string.

My code looks like this:

PreparedStatement stmt = null;
String sql = "SELECT table_field FROM table_name WHERE NVL(table_field, '') LIKE ? ";
stmt = connection.prepareStatement(sql);
stmt.setString(myEmptyString, 1);

I have NULL values in my table_field but my query doesn't return anything when I execute it. What am I missing ?

I currently use a workaround with WHERE table_field IS NULL but I want to check empty strings as well.

Answer 1

In Oracle an empty string is the same as null, so NVL(table_field, '') is the same as NVL(table_field, null), which doesn't achieve anything. If the field was null then it stays null.

You also can't compare null to anything. If you want to include null values in the result set then you can do:

WHERE table_field IS NULL OR table_field LIKE ?

If the bind parameter might be null (or an empty string) and you only want to match nulls when it is then you can do:

WHERE (? IS NULL AND table_field IS NULL) OR (table_field LIKE ?)

but you then need to supply the bind value twice.

Using LIKE only makes sense if you bind value has wildcards and you want to find partial matches. If you're looking for exact matches then use = instead of LIKE. It may not affect the result set, but it could in unexpected ways, and it makes the intent clearer.