I have a field (A22 format) which can contain a series of either numbers or letters. What is the best method to remove all occurances of alpha characters from the field so that only numbers are remaining? The letters may occur in any positions within the field: (ex - 123AA or AA123 or 1A2A3).

Thanks in advance.

This message has been edited. Last edited by: Kerry,

Take a look at this post, SQL Code conversion in Focus for an example of a DEFINE FUNCTION that does what you require.

Thx Francis. I was looking for a method similar to using regular expressions within FOCUS. I'll check it out.

As far as I know, there are no regular expressions, but there are many ways to do something.

Here's another way, using three built-in functions, PATTERN, STRREP and EDIT. Note this will only work if the column contains only alpha or numeric characters - PATTERN leaves special characters untouched.

TABLE FILE EMPDATA
PRINT 
JOBCLASS
COMPUTE JPATTERN1/A8 = PATTERN(8, JOBCLASS, 'A8');
COMPUTE JPATTERN2/A8 = STRREP(8, JPATTERN1, 1, 'A', 1, '$', 8, 'A8');
COMPUTE JNUM/A8 = EDIT(JOBCLASS,JPATTERN2);
END

I've left out the NOPRINT for JPATTERN1 and JPATTERN2 so you can see the transformations. First I use PATTERN to determine where the alpha and numeric characters are. Then I use STRREP to replace the 'A' (for alpha) by '$'. Then I use EDIT to extract the numeric characters ('$' is a mask character which excludes the alpha characters). Pretty nifty until someone comes up with a better idea.

Awesone solution Francis.

Agreed. Awesome solution. Thanks!

Ditto Clever use of WebFOCUS! and outside the box.

I think this is an excellent example of WebFOCUS capabilities.

I'm happy that my brain is still FOCUSing after all these years.

Hopefully without assistance.