On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

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