Is there a built-in function to test for alpha or numeric data in Informix 7.3?

Question

I have been searching around for a SQL function that tests a value for being alpha or for being numeric. There doesn't seem to be any functions like this for Informix 7.3, but I might have missed something or have been searching for the wrong stuff.

For example, I'm trying to test a phone number field for having only numbers in it and dealing with it accordingly:

Given this table with the phone field type being char(12):

id      name        phone
5164      Cheese      973-153-5149
8843      Queso       (201)8011830
8356      Formaggio   2129182943
8938      Ost         ext.21438
3852      Ser         973-15-1549

I want to do something like this:

SELECT  CASE    WHEN    (ISALPHA((TRIM(REPLACE((REPLACE((REPLACE((REPLACE((REPLACE(phone,'-','')),'(','')),')','')),' ','')),'.','')))))
                THEN    'has alpha chars'
                WHEN    (LENGTH((TRIM(REPLACE((REPLACE((REPLACE((REPLACE((REPLACE(phone,'-','')),'(','')),')','')),' ','')),'.','')))) NOT IN (10,11))
                THEN    'wrong length'
                WHEN    (phone IN ('',' ',NULL)))
                THEN    'phone is blank'
                ELSE    (TRIM(REPLACE((REPLACE((REPLACE((REPLACE((REPLACE(phone,'-','')),'(','')),')','')),' ','')),'.','')))
        END     phone
FROM    given_table;

Basically, I'm trying to remove any -, (, ), , . chars from the phone number, trim that result, test it's length for being either 10 or 11 characters long, and making sure that there is no alpha chars in the data after all the replaces and trim. (If you feel that anything is wrong with how I'm going about replacing & trimming, I'm open for suggestions on that aspect as well. Regex?)

The end result that I would want would be:

phone
9731535149
2018011830
2129182943
has alpha chars
wrong length

I've read one solution (Oracle) that suggests using a combination of LENGTH, TRIM, and TRANSLATE but TRANSLATE is not supported for Informix 7.3

---

*Please note - I will write my own function if none exist, so please do not suggest that as an answer. I just want to know if there are any built-in ways for doing this.

---

Jonathan Leffler to the rescue?

Answer 1

Here is a way I found to test for alpha. It's a work-around, but I think it is reliable so far.

SELECT phone FROM given_table WHERE UPPER(phone)<>LOWER(phone);

Reference: http://www.geekinterview.com/question_details/34549 (see answer from the user "amarnathtnl" a little more than halfway down the page)