Jotting #12: Find empty strings in Oracle table

Had to find some strings (varchar2) in a table that were just blanks with optional end-of-line characters thrown in.

Luckily, regular expressions make that an easy task. Here is the query:

SELECT *
  FROM myTable x
 WHERE REGEXP_LIKE( x.myColumn, '(^[[:space:]]*$)' );

A short explanation:

• ^...$ says that pattern applies to the string from start to finish, i.e., it’s not just a sub-string pattern,
• [...]* says that pattern occurs 0 or more times (could also have been [...]+ in this case),
• [:space:] defines a pattern of all white-space characters, including blank, \t, \r and \n.

Sometimes, regular expression just make tasks like these very easy.