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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: