Tuesday, February 10, 2009

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

No comments:

Post a Comment