List Rows with at Least One Alpha Character

How this is done is based on the version. If you're running 10 or greater, you can use a regular expression. The first example looks for any alphabetic character; the second for anything not numeric.

10g Example 1:

Select Postal_Code
  From Hr.Locations
 Where Regexp_Like(Postal_Code,'[[:alpha:]]{1}');

POSTAL_CODE
------------
M5V 2L7
YSW 9T2
OX9 9ZB
3029SK

10g Example 2:

Select Postal_Code
  From Hr.Locations
 Where Not Regexp_Like(Postal_Code, '^[0-9]+$');

POSTAL_CODE
------------
M5V 2L7
YSW 9T2
OX9 9ZB
01307-002
3029SK

If you're using a version before 10, you'll have to use a translate. In this case, I'm translating all the numbers to a space, trimming them out, and then seeing if it has a length. Notice that this one picks up a zip that has a dash, where the RegExp is looking for purely A-Z.

SQL>
Select Postal_Code
  From HR.Locations
 Where Length(Trim(Translate(Postal_Code,'0123456789','          ')))>= 1;

POSTAL_CODE
------------
M5V 2L7
YSW 9T2
OX9 9ZB
01307-002
3029SK